package com.szholly.pro.watergas_server.ps.service;

import java.io.OutputStream;
import java.text.DecimalFormat;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.common.util.PollCodeCompare;
import com.szholly.pro.watergas_server.common.util.RedisHelper;
import com.szholly.pro.watergas_server.common.util.RowBaseCompare;
import com.szholly.pro.watergas_server.common.util.Y2YM2MDateModel;
import com.szholly.utils.spring.SpringBeanFactory;
import com.szholly.utils.util.StringUtils;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.ITable;
import com.szholly.data.general.ITableFactory;
import com.szholly.data.general.RowBase;

import lrq.common.StringUtil;
import net.sf.ehcache.search.expression.And;
import net.sf.json.JSONObject;

/**
 * 数据分析service
 * @author gislin
 * 2016年4月27日
 */
@Service
public class WaterQualityAnalysisService { 

	@Autowired
	DomainService domainService;
	
	@Autowired
	OnlineMonitorService onlineMonitorService;
	@Autowired
	DataReportService dataReportService;
	
	/**
	 * 获取趋势分析报表
	 * gislin
	 * 2016年8月29日
	 * @param startDate
	 * @param endDate
	 * @param cycleTypeStr
	 * @param stationCodes
	 * @param mulitPolls
	 * @return
	 * @throws ParseException 
	 */
	public JSONObject queryTrend(Date startDate,Date endDate,String cycleTypeStr,String[] stationCodes,HashMap<String,List<String>> mulitPolls) throws ParseException{
		String tableName = "";
		
		if ("day".equals(cycleTypeStr)) {
			tableName = "monitor_data_day";
		}else if ("week".equals(cycleTypeStr)) {
			tableName = "monitor_data_week";
		}else if("month".equals(cycleTypeStr)){
			tableName = "monitor_data_month";
		} else if ("quarter".equals(cycleTypeStr)) {
			tableName = "monitor_data_quarter";
		}else if ("year".equals(cycleTypeStr)) {
			tableName = "monitor_data_year";
		}
/*		ProduceWaterTypeByStadard(startDate,endDate,cycleTypeStr);*/
/*		produceCompPollIndexByStadard(startDate,endDate,cycleTypeStr);*/
		
		List<RowBase> riverNames = getMulitPollRiver(stationCodes,mulitPolls);
		List<RowBase> sections = getMulitPollRiverSection(stationCodes,mulitPolls);
		List<RowBase> stationNames = getMulitPollStations(stationCodes,mulitPolls);
		List<RowBase> pollNameList = getMulitPollNames(mulitPolls,stationNames);
		List<String> pollDeduplicated = getPollDeduplicated(pollNameList);
		List<RowBase> pollDeduplicatedList = getPollNames(pollDeduplicated.toArray(new String[pollDeduplicated.size()]));
		
		
		
		String[] stationMNs = new String[stationCodes.length];
		//获取mn集合
		for(int i=0;i<stationCodes.length;i++){
			String mn = RedisHelper.getInstance().getMNByStationCode(stationCodes[i]);
			stationMNs[i] = mn;
		}
		
		//获取mn的in where语句和参数
		String mnInWhere = StringUtil.getArrayStrForSql(stationMNs, "mn");
		List<GlobalParameter> pc1 = DataUtil.arrayToParameters(stationMNs, "mn");
		String pollCodeInWhere = StringUtil.getArrayStrForSql(pollDeduplicated.toArray(new String[pollDeduplicated.size()]), "pollCode");
		pc1.addAll(DataUtil.arrayToParameters(pollDeduplicated.toArray(new String[pollDeduplicated.size()]), "pollCode"));
		pc1.add(new GlobalParameter("startDate", startDate));
		pc1.add(new GlobalParameter("endDate", endDate));
		
		String dateTextsql = " SELECT distinct trunc(datatime) time ,DATE_TEXT FROM  "+tableName+" WHERE DATATIME >= @startDate and DATATIME <= @endDate "+
				 " and mn in "+mnInWhere +
				 " and parmname in "+pollCodeInWhere+" order by time,DATE_TEXT asc ";
		
		List<String> dateTextList = DataUtil.getSelectStringRows(dateTextsql, pc1, "DATE_TEXT");
		String dateTexts = "";
		for(String dateText : dateTextList){
			dateTexts += ",'"+dateText+"' P"+dateText;
		}
		dateTexts = dateTexts.replaceFirst(",", "");
		
		String sql = " select * from ( select * from (\n" +
						"  select value_type_ ,value,mn_ ,parmname_,date_text,RIVER_CODE_,RIVER_SECTION_ from (\n" + 
						"         select  max,min,avg,mn mn_ ,parmname parmname_,date_text,RIVER_CODE  RIVER_CODE_,RIVER_SECTION RIVER_SECTION_ from  "+tableName+ " md"+
						"			 left join BC_COLLECTION_INFO dc on dc.device_num = md.mn "+
						"			 left join BC_SITE_INFO ms on ms.code = dc.site_id  "+
						"			where md.parmname in "+pollCodeInWhere +
						"				and md.DATATIME >= @startDate " +
						"				and md.DATATIME <= @endDate \n" + 
						" 				and md.mn in "+mnInWhere+
						"  ) unpivot (value for value_type_ in (max,min,avg))\n" + 
						" )  pivot(avg(value) for date_text in ("+dateTexts+")) ) t";
		
		String forAllDataSql = "";
		for(RowBase station:stationNames){
			String mn = station.getSafeString("MN");
			String stationCode = station.getSafeString("CODE");
			String riverCode = station.getSafeString("RIVER_CODE");
			String riverSection = station.getSafeString("RIVER_SECTION");
			for(String pollCode:pollDeduplicated){
				forAllDataSql += "\n SELECT '"+stationCode+"' STATION_CODE, '"+mn+"' mn,'"+riverCode+"' river_code,'"+riverSection+"' river_section,'"+pollCode+"' parmname,'AVG' value_type FROM DUAL \n"
						+ " union all";
				forAllDataSql += "\n SELECT '"+stationCode+"' STATION_CODE, '"+mn+"' mn,'"+riverCode+"' river_code,'"+riverSection+"' river_section,'"+pollCode+"' parmname,'MAX' value_type FROM DUAL \n"
						+ " union all";
				forAllDataSql += "\n SELECT '"+stationCode+"' STATION_CODE, '"+mn+"' mn,'"+riverCode+"' river_code,'"+riverSection+"' river_section,'"+pollCode+"' parmname,'MIN' value_type FROM DUAL \n"
						+ " union all";
			}
		}
		forAllDataSql = StringUtil.replaceLast(forAllDataSql, "union all", "");
		forAllDataSql = " (SELECT * FROM ("+forAllDataSql+")) t1 ";
		
		sql = sql + " \n right join \n " + forAllDataSql + " on t.mn_ = t1.mn and t1.value_type=t.value_type_ and t.parmname_ = t1.parmname";
		
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("POLLDATA");
		List<RowBase> list = DataUtil.getSelectRows(sql, pc1,pTable);
		
		Iterator<RowBase> resultListItera = list.iterator();
		while(resultListItera.hasNext()){
			RowBase dataRow = resultListItera.next();
			String stationCode = dataRow.getSafeString("STATION_CODE");
			String curPollCode = dataRow.getSafeString("PARMNAME");
			String valueType = dataRow.getSafeString("VALUE_TYPE");
			List<String> curPollList = mulitPolls.get(stationCode);
			if(!curPollList.contains(curPollCode)){
				System.out.println(stationCode+","+curPollCode+","+valueType);
				resultListItera.remove();
			}
		}
		
		//对数据进行排序
		RowBaseCompare rowBaseComparer = new RowBaseCompare();
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("RIVER_CODE"));
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("RIVER_SECTION"));
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("MN"));
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("PARMNAME"));
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("VALUE_TYPE"));
		Collections.sort(list, rowBaseComparer);
		
		JSONObject resultJsonObject = new JSONObject();
		resultJsonObject.put("river", riverNames);
		resultJsonObject.put("riverSection", sections);
		resultJsonObject.put("stations", stationNames);
		resultJsonObject.put("polls", pollNameList);
		resultJsonObject.put("dateTexts", dateTextList);
		resultJsonObject.put("data", list);
		resultJsonObject.put("pollDeduplicated",pollDeduplicatedList);
		
		return resultJsonObject;
	} 
	
	public JSONObject queryFlux1(Date startDate,Date endDate,String cycleTypeStr,String[] stationCodes,String snStationCode,String[] pollCodes){
		
		HashMap<String,Object> conData = queryFluxData(startDate, endDate, cycleTypeStr, stationCodes, pollCodes);
		HashMap<String,Object> snData = queryFluxData(startDate, endDate, cycleTypeStr,new String[]{snStationCode}, pollCodes);
		
		@SuppressWarnings("unchecked")
		List<RowBase> conDataRows = (List<RowBase>) conData.get("data");
		@SuppressWarnings("unchecked")
		List<RowBase> snDataRows = (List<RowBase>) snData.get("data");
		RowBase snDataRow = snDataRows.get(0);
		@SuppressWarnings("unchecked")
		List<String> dateList = (List<String>) conData.get("dateTexts");
		
		DecimalFormat df = new DecimalFormat("0.00");
		
		for(RowBase conRow : conDataRows){
			for(String dateText:dateList){
				Double snCou = snDataRow.getDouble("COU"+dateText);
				Double curCou = conRow.getDouble("COU"+dateText);
				if(snCou == null || curCou == null){
					conRow.setValue("pre"+dateText, "-");
				}
				else{
					conRow.setValue("pre"+dateText, df.format(100 * curCou/snCou));
				}
			}
		}
		
		JSONObject resultObject = new JSONObject();
		resultObject.put("conData", conData);
		resultObject.put("snData", snData);
		return resultObject;
	}
	
	private HashMap<String,Object> queryFluxData(Date startDate,Date endDate,String cycleTypeStr,String[] stationCodes,String[] mulitPolls){
		String tableName = "";
		
		if ("day".equals(cycleTypeStr)) {
			tableName = "monitor_data_day";
		}else if ("week".equals(cycleTypeStr)) {
			tableName = "monitor_data_week";
		}else if("month".equals(cycleTypeStr)){
			tableName = "monitor_data_month";
		} else if ("quarter".equals(cycleTypeStr)) {
			tableName = "monitor_data_quarter";
		}else if ("year".equals(cycleTypeStr)) {
			tableName = "monitor_data_year";
		}
		
		List<RowBase> riverNames = getRiver(stationCodes);
		List<RowBase> sections = getRiverSection(stationCodes);
		List<RowBase> stationNames = getStations(stationCodes);
		List<RowBase> pollNameList = getPollNames(mulitPolls);
		
		String[] stationMNs = new String[stationCodes.length];
		//获取mn集合
		for(int i=0;i<stationCodes.length;i++){
			String mn = RedisHelper.getInstance().getMNByStationCode(stationCodes[i]);
			stationMNs[i] = mn;
		}
		
		//获取mn的in where语句和参数
		String mnInWhere = StringUtil.getArrayStrForSql(stationMNs, "mn");
		List<GlobalParameter> pc1 = DataUtil.arrayToParameters(stationMNs, "mn");
		String pollCodeInWhere = StringUtil.getArrayStrForSql(mulitPolls, "pollCode");
		pc1.addAll(DataUtil.arrayToParameters(mulitPolls, "pollCode"));
		pc1.add(new GlobalParameter("startDate", startDate));
		pc1.add(new GlobalParameter("endDate", endDate));
		
		String dateTextsql = " select distinct trunc(datatime) time ,DATE_TEXT FROM "+tableName+" WHERE DATATIME >= @startDate and DATATIME <= @endDate "+
				 " and mn in "+mnInWhere +
				 " and parmname in "+pollCodeInWhere+" order by time,DATE_TEXT asc";
		
		List<String> dateTextList = DataUtil.getSelectStringRows(dateTextsql, pc1, "DATE_TEXT");
		
		String dateTexts = "";
		for(String dateText : dateTextList){
			dateTexts += ",'"+dateText+"' P"+dateText;
		}
		dateTexts = dateTexts.replaceFirst(",", "");
		
		String dateTextsB01 = "";
		for(String dateText : dateTextList){
			dateTextsB01 += ",'"+dateText+"' B01"+dateText;
		}
		dateTextsB01 = dateTextsB01.replaceFirst(",", "");

		String dateTextsCOU = "";
		for(String dateText : dateTextList){
			dateTextsCOU += ",'"+dateText+"' COU"+dateText;
		}
		dateTextsCOU = dateTextsCOU.replaceFirst(",", "");
		
		//获取数据
		String sql = " select * from( SELECT *\n" +
						"  FROM (select *\n" + 
						"          from (select DATE_TEXT, PARMNAME PARMNAME_,ms.river_code river_code_,ms.river_section river_section_, ROUND(avg,2) avg, mn mn_\n" + 
						"                  from "+tableName+" t1\n" + 
						"      					left join BC_COLLECTION_INFO dc on dc.device_num = t1.mn \n"+
						"                        left join BC_SITE_INFO ms on ms.code = dc.site_id \n"+
						"                 where mn in\n" + 
						"                      "+mnInWhere+"\n" + 
						"                   and parmname in "+pollCodeInWhere+") t pivot(sum(avg) for DATE_TEXT in ("+dateTexts+"))) t\n" + 
						" ) t"
						+ " left join (" +
						"	select *\n" +
						"         from (select DATE_TEXT,AVG, mn B01mn_\n" + 
						"                 from monitor_data_day t1\n" + 
						"                       left join BC_COLLECTION_INFO dc on dc.device_num = t1.mn\n" + 
						"                       left join BC_SITE_INFO ms on ms.code = dc.site_id\n" + 
						"                where mn in\n" + 
						"                      "+mnInWhere+"\n" + 
						"                  and parmname ='B01TOTAL') t pivot(sum(avg) for DATE_TEXT in ("+dateTextsB01+"))"+
						") tt ON tt.B01mn_ = t.mn_ "
						+ " left join (" +
						"	select *\n" +
						"         from (select DATE_TEXT,PARMNAME PARMNAME_cou,round(cou,2) cou, mn coumn_\n" + 
						"                 from monitor_data_day t1\n" + 
						"                       left join BC_COLLECTION_INFO dc on dc.device_num = t1.mn\n" + 
						"                       left join BC_SITE_INFO ms on ms.code = dc.site_id\n" + 
						"                where mn in\n" + 
						"                      "+mnInWhere+"\n" + 
						"                  and parmname in "+pollCodeInWhere+") t pivot(sum(cou) for DATE_TEXT in ("+dateTextsCOU+"))"+
						") tou ON tou.coumn_ = t.mn_ and tou.PARMNAME_cou = t.PARMNAME_ ";
		
		String forAllDataSql = "";
		for(RowBase station:stationNames){
			String mn = station.getSafeString("MN");
			String stationName = station.getSafeString("NAME");
			String riverCode = station.getSafeString("RIVER_CODE");
			String riverSection = station.getSafeString("RIVER_SECTION");
			for(RowBase poll:pollNameList){
				String pollCode = poll.getSafeString("pollCode");
				forAllDataSql += "\n SELECT '"+stationName+"' station_name, '"+mn+"' mn,'"+riverCode+"' river_code,'"+riverSection+"' river_section,'"+pollCode+"' parmname,'AVG' value_type FROM DUAL \n"
								+" union all";
			}
		}
		forAllDataSql = StringUtil.replaceLast(forAllDataSql, "union all", "");
		forAllDataSql = " (SELECT * FROM ("+forAllDataSql+")) t1 ";
		
		sql = sql + " \n right join \n " + forAllDataSql + " on t.mn_ = t1.mn and t.parmname_ = t1.parmname";
		
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("POLLDATA");
		List<RowBase> list = DataUtil.getSelectRows(sql, pc1,pTable);
		
		for(RowBase row:list){
			for(String dateText : dateTextList){
				Double avg = row.getDouble("P"+dateText);
				Double b01 = row.getDouble("B01"+dateText);
				Double cou = row.getDouble("COU"+dateText);
				if(avg == null){
					row.setValue("P"+dateText, "-");
				}
				if(b01 == null){
					row.setValue("B01"+dateText, "-");
				}
				if(cou == null){
					row.setValue("cou"+dateText, "-");
				}
			}
		}
		
		//对数据进行排序
		RowBaseCompare rowBaseComparer = new RowBaseCompare();
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("RIVER_CODE"));
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("RIVER_SECTION"));
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("MN"));
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("PARMNAME"));
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("VALUE_TYPE"));
		Collections.sort(list, rowBaseComparer);
		
		HashMap<String,Object> result = new HashMap<>();
		
		result.put("river", riverNames);
		result.put("riverSection", sections);
		result.put("stations", stationNames);
		result.put("polls", pollNameList);
		result.put("dateTexts", dateTextList);
		result.put("data", list);
		
		return result;
	}
	
	/*
	 * 查询水质数据类别
	 */
	public HashMap<String, Object> getWaterType(Date starts,String startDate,String endDate,int dateLength,boolean isY2Y,String cycleType,String[] mns,String[] stationIds,String pollCode,String[] poll,String river,HashMap<String,List<String>> mulitPolls) throws Exception{
		//站点为空或者污染物为空值，直接返回空，污染物只有一个空字符串也返回空。
		if(mns == null||poll==null||(poll.length==1&& "".equals(poll[0]))){
			return null;
		}	
		//污染物名称转译
		String tableName = "";
		String stationName ="";
		List<String> pollName= new ArrayList<>();
		for(int i=0;i<poll.length;i++) {
			String Names = RedisHelper.getInstance().translationDictValue("MONITOR_PARAM", poll[i]);
			pollName.add(Names);
		}
		//切换表名称
		String end=endDate;
		 end = end+" 23:59:59";
		 String labelName="";
		if("day".equals(cycleType)) {
			tableName = "day_water_type";
			labelName="（天）";
		}else if("month".equals(cycleType)){
			tableName = "month_water_type";
			labelName="（月）";
		}else if("week".equals(cycleType)) {
			tableName = "week_water_type";
			labelName="（周）";
		}else if("quarter".equals(cycleType)) {
			tableName = "quarter_water_type";
			labelName="（季度）";
		}else if("year".equals(cycleType)) {
			tableName = "year_water_type";
			labelName="（年）";
		}
		//根据站点id查询 河流信息（包括站点数目），河段包括多少个站点，每个站点包括多少个污染物
		List<RowBase> riverNames = getMulitPollRiver(stationIds,mulitPolls);
		List<RowBase> sections = getMulitPollRiverSection(stationIds,mulitPolls);
		List<RowBase> stationNames = getMulitPollStations(stationIds,mulitPolls);
		List<RowBase> pollNameList = getMulitPollNames(mulitPolls,stationNames);
		List<String> pollDeduplicated = getPollDeduplicated(pollNameList);
		String dateTextsql = "";
		String pollCodeInWhere = StringUtil.getArrayStrForSql(pollDeduplicated.toArray(new String[pollDeduplicated.size()]), "pollCode");
		String mnInWhere = StringUtil.getArrayStrForSql(mns, "mn");
		List<GlobalParameter> pc = DataUtil.arrayToParameters(mns, "mn");

		pc.add(new GlobalParameter("startDate", startDate));
		pc.add(new GlobalParameter("endDate", endDate));
		pc.addAll(DataUtil.arrayToParameters(pollDeduplicated.toArray(new String[pollDeduplicated.size()]), "pollCode"));
		//构建查询表格中time项SQL
		dateTextsql = " SELECT distinct trunc(datatime) time ,DATA_TEXT FROM "+tableName+" WHERE DATATIME >= to_date('"+startDate+"','yyyy-mm-dd') and DATATIME <= to_date('"+end+"','yyyy-mm-dd hh24:mi:ss') "+
					 " and mn in "+mnInWhere +
					 " and parmname in "+pollCodeInWhere+" order by time,DATA_TEXT asc";
		//查询time
		List<RowBase> dateTextList = DataUtil.getSelectRows(dateTextsql, pc);
		
		//特意为水质类别分析组装污染物参数列。
		List<RowBase> pollNameLists = getPollNames(mulitPolls,stationNames,dateTextList);
		//构建查询水质类别项SQL
		String sql = " select mn mn_,data_text data_text_,parmname parmname_,value,water_type from "+tableName+" t "
				+ "	left join BC_COLLECTION_INFO dc on dc.device_num = t.mn \n"
				+ " left join BC_SITE_INFO ms on ms.code = dc.site_id \n"+ 
				"where 1=1 ";
		if(pollCodeInWhere!=null&&!"''".equals(pollCodeInWhere)) {
			sql+= " and t.parmname in "+pollCodeInWhere+"";
		} 
		if(mnInWhere!=null&&!"''".equals(mnInWhere)) {
			 sql+= "and mn in "+mnInWhere;
		} 
		 if (startDate!=null && end!=null) {
			 sql+= " and datatime >= to_date('"+startDate+"'"+",'yyyy-MM-dd')";
			 sql+= " and datatime<= to_date('"+end+"'"+",'yyyy-MM-dd hh24:mi:ss')";
		 }
		 List<String> name = new ArrayList<>();
		 if(mns!=null && mns.length>0) {
			 for(int i = 0;i < mns.length; i++) {
				stationName = RedisHelper.getInstance().getStationNameByMN(mns[i]);
				name.add(stationName);
			 }
		 }
		 sql="select * from ("+sql+") t";
			String forAllDataSql = "";
			for(int k=0;k< stationNames.size();k++){
				RowBase station = stationNames.get(k);
				String MNS = station.getSafeString("MN");
				String stationCode = station.getSafeString("CODE");
				String riverCode = station.getSafeString("RIVER_CODE");
				String riverSection = station.getSafeString("RIVER_SECTION");
				for(int i=0;i< dateTextList.size();i++) {
					String date = dateTextList.get(i).getSafeString("DATA_TEXT");
					for(int m=0;m<pollDeduplicated.size();m++) {
						String pollCodes = pollDeduplicated.get(m);
						int nn = i+k*dateTextList.size();
						forAllDataSql += "\n SELECT '"+stationCode+"' STATION_CODE, '"+MNS+"' mn,'"+riverCode+"' river_code,'"+riverSection+"' river_section,'"+pollCodes+"' parmname,'AVG' value_type,'"+date+"' date_text,'"+nn+"' Num FROM DUAL \n"
								+ " union all";
					}
				}
			}
			if(!"".equals(forAllDataSql)) {
				forAllDataSql = StringUtil.replaceLast(forAllDataSql, "union all", "");
				forAllDataSql = " (SELECT * FROM ("+forAllDataSql+")) t1 ";
				sql = sql + " \n right join \n " + forAllDataSql + " on  t.mn_ = t1.mn and t.parmname_ = t1.parmname and t.data_text_=t1.date_text\n" 
			 +" ORDER BY t1.river_code,t1.river_section,t1.mn,t1.date_text,T1.parmname";
			}
		 HashMap<String, Object> map = new HashMap<>();
		//查询水质类别信息
		List<RowBase> pollWaterTypelist = DataUtil.getSelectRows(sql, pc);
		
		Iterator<RowBase> resultListItera = pollWaterTypelist.iterator();
		
		while(resultListItera.hasNext()){
			RowBase dataRow = resultListItera.next();
			String stationCode = dataRow.getSafeString("STATION_CODE");
			String curPollCode = dataRow.getSafeString("PARMNAME");
			List<String> curPollList = mulitPolls.get(stationCode);
			if(!curPollList.contains(curPollCode)){
				resultListItera.remove();
			}
		}

		//地表水
		Map<String,String> params = new HashMap<>();
		params.put("category", "SURFACE_WATER");
		for(int i=0;i<pollWaterTypelist.size();i++) {
			String parmname="";
			String WaterType = pollWaterTypelist.get(i).getSafeString("WATER_TYPE");
			parmname = pollWaterTypelist.get(i).getSafeString("parmname");
			String Names = RedisHelper.getInstance().translationDictValue("MONITOR_PARAM", parmname);
			String pollUnit = RedisHelper.getInstance().translationDictUnit("MONITOR_PARAM", parmname);

			pollWaterTypelist.get(i).setValue("water_type", WaterType);
			pollWaterTypelist.get(i).setValue("parm_name", Names+"("+pollUnit+")");
		}
		List<String> dateTextStr = new ArrayList<String>();
		for(int i=0;i<dateTextList.size();i++) {
			String dateText = dateTextList.get(i).getSafeString("DATA_TEXT");
			dateTextStr.add(dateText);
		}
		String[] stationCode = new String[stationNames.size()];
		for(int k=0;k<stationNames.size();k++) {
			RowBase row = stationNames.get(k);
			String Code = row.getSafeString("CODE");
			stationCode[k]=Code;
		}
		//获取站点水质类别
		List<RowBase> stationType = getWorstWaterType(stationCode,stationNames,dateTextStr,pollWaterTypelist,mulitPolls);
		//获取河段水质类别
		List<RowBase> riverSectionType = getRiverSectionType(sections,stationType);
		//获取综合污染指数
		if("day".equals(cycleType)) {
			tableName = "monitor_data_day";
		} else if("week".equals(cycleType)){
			tableName = "monitor_data_week";
		}else if("month".equals(cycleType)) {
			tableName = "monitor_data_month";
		}else if("quarter".equals(cycleType)) {
			tableName = "monitor_data_quarter";
		}else if("year".equals(cycleType)) {
			tableName = "monitor_data_year";
		}
		String comPollIndexSql = "SELECT ms.river_code,ms.river_section,mn mn_,parmname parmname_,avg,datatime,date_text date_text_ from "+tableName+" t "
				+ " left join BC_COLLECTION_INFO dc on dc.device_num = t.mn \n"+
				"   left join BC_SITE_INFO ms on ms.code = dc.site_id \n"+
				"where parmname='COMP_POLL_INDEX'";
		 if (startDate!=null && end!=null) {
			 comPollIndexSql+= " and datatime >= to_date('"+startDate+"'"+",'yyyy-MM-dd')";
			 comPollIndexSql+= " and datatime<= to_date('"+end+"'"+",'yyyy-MM-dd hh24:mi:ss')";
		 }
		 if(mnInWhere!=null && !"".equals(mnInWhere)) {
			 comPollIndexSql+= "and mn in "+mnInWhere;
		 }
		 
		 comPollIndexSql="select * from ("+comPollIndexSql+") t";
			String forAllDataSqls = "";
			for(int k=0;k< stationNames.size();k++){
				RowBase station = stationNames.get(k);
				String MNS = station.getSafeString("MN");
				String stationCodes= station.getSafeString("CODE");
				String riverCode = station.getSafeString("RIVER_CODE");
				String riverSection = station.getSafeString("RIVER_SECTION");
				for(int i=0;i< dateTextList.size();i++) {
					String dates = dateTextList.get(i).getSafeString("DATA_TEXT");
						int nn = i+k*dateTextList.size();
						forAllDataSqls += "\n SELECT '"+stationCodes+"' STATIONCODE, '"+MNS+"' mn,'"+riverCode+"' river_code,'"+riverSection+"' river_section,'COMP_POLL_INDEX' value_type,'"+dates+"' date_text,'"+nn+"' Num FROM DUAL \n"
								+ " union all";
				}
			}
			if(!"".equals(forAllDataSqls)) {
				forAllDataSqls = StringUtil.replaceLast(forAllDataSqls, "union all", "");
				forAllDataSqls = " (SELECT * FROM ("+forAllDataSqls+")) t1 ";

			 comPollIndexSql = comPollIndexSql + " \n right join \n " + forAllDataSqls + " on t.mn_ = t1.mn and  t.date_text_=t1.date_text "
			 			+" ORDER BY t1.river_code,t1.river_section,t1.mn,t1.date_text";
			}
		 //通过综合污染指数判断是几类水质，然后得出结果：好，较好，轻度污染，中度污染，重污染，严重污染。

		 List<RowBase> pollIndex = DataUtil.getSelectRows(comPollIndexSql, pc);
		 for(int i=0;i<pollIndex.size();i++) {
			 DecimalFormat df = new DecimalFormat("#0.00");
			 double index = pollIndex.get(i).getSafeDouble("avg");
			 pollIndex.get(i).setValue("pollIndex", df.format(index));
			String MN = pollIndex.get(i).getSafeString("MN");
			String StaName = RedisHelper.getInstance().getStationNameByMN(MN);
			 //计算综合污染指数，得到水质状况。
			 String quality="";
			 quality=getPollIndexResult(index);
			 pollIndex.get(i).setValue("quality", quality);
			 pollIndex.get(i).setValue("staName", StaName);
		 }
/*		//数据构造部分，为了配合前台数据显示
		for(int k=0;k<pollIndex.size();k++) {
			double indexValue = pollIndex.get(k).getSafeDouble("pollIndex");
			String waterQuality="";
			String statType="";
			if(0.0<indexValue&&indexValue<0.20) {
				waterQuality="好";
				statType="I";
			} else if(0.2<=indexValue&&indexValue<0.40){
				waterQuality="较好";
				statType="II";
			}else if(0.40<=indexValue&&indexValue<0.70){
				waterQuality="轻度污染";
				statType="III";
			}else if(0.70<=indexValue&&indexValue<1.0){
				waterQuality="中度污染";
				statType="IV";
			}else if(1.0<=indexValue&&indexValue<2.0){
				waterQuality="重污染";
				statType="V";
			}else if(2.0<=indexValue){
				waterQuality="严重污染";
				statType="劣V";
			}
			//根据站点水质类别修改水质状况
			pollIndex.get(k).setValue("QUALITY", waterQuality);
			//根据站点水质类别修改综合污染指数。
			pollIndex.get(k).setValue("STATIONTYPE", statType);
		}*/
		//添加综合污染指数对应的参数个数
		pollIndex=getPollIndex(pollIndex,mulitPolls);
		List<RowBase> riverSectionPollIndex = getRiverSectionPollIndex(stationNames,sections,pollIndex);
			map.put("riverList",riverNames);
			map.put("stationName",stationNames);
			map.put("pollNameList",pollNameList);		
			map.put("waterType",pollWaterTypelist);
			map.put("timeLength",dateTextList.size());
			map.put("dateText",dateTextList);
			map.put("time",dateTextList);
			map.put("stationWaterType",stationType);
			map.put("sections",riverSectionType);
			map.put("sectionPollIndex",riverSectionPollIndex);
			map.put("compPollIndex",pollIndex);
			map.put("labelName",labelName);
			map.put("polls", pollNameLists);
		return map;
	}
	/**
	 * 通过计算污染指数得到水质状况
	 * @param index
	 * @return
	 */
	private String getPollIndexResult(double index) {		
		String quality="";
		 if(index > 0.0 && index <=0.20) {
			 quality="好";	
		 } else if(index > 0.20 && index <=0.4) {
			 quality="较好";	
		 }else if(index > 0.40 && index <=0.70) {
			 quality="轻度污染";	
		 }else if(index > 0.70 && index <=1.0) {
			 quality="中度污染";	
		 }else if(index > 1.00 && index <=2.0) {
			 quality="重污染";	
		 }else if(index > 2.00 && index <=9999.99) {
			 quality="严重污染";	
		 }
		 return quality;
	}
	/**
	 * 根据数值比对，得出变化率，最终得出污染程度的变化结果。
	 * @param firstIndex
	 * @param nextIndex
	 * @return
	 */
	private String getPollIndexChangeResult(double firstIndex,double nextIndex) {
		String result="";
		double rst=0.0;
		//如果比较的前者为空，则没有变化。
		if (firstIndex==0.0||nextIndex==0.0) {
			return "";
		}
		rst = (nextIndex-firstIndex)/firstIndex;
		if(Math.abs(rst)<0.1) {
			result="污染程度稳定";
		} else if (rst>=0.1&&rst<0.25) {
			result="污染程度有所加重";
		} else if(rst>=-0.25&&rst<-0.1) {
			result="污染程度有所减轻";
		} else if(rst>=0.25&&rst<0.5) {
			result="污染程度明显加重";
		} else if(rst>=-0.5&&rst<-0.25) {
			result="污染程度明显减轻";
		} else if(rst>=0.5) {
			result="污染程度显著加重";
		} else if(rst<-0.5) {
			result="污染程度显著减轻";
		}
		 return result;
	}
	
	/**
	 * 通过污染物的水质类别，以最差的一个作为该站点的水质类别
	 * @param stationid
	 * @param time
	 * @param list
	 * @return
	 */
	private List<RowBase> getWorstWaterType(String[] stationCodes,List<RowBase> stationid,List<String> time,List<RowBase> list,HashMap<String, List<String>> mulitPolls) {
		List<RowBase> stationWaterType= new ArrayList<>();
		for(int j=0;j<stationCodes.length;j++) {
			RowBase row = new RowBase();
			row = stationid.get(j);
			String riverCode = row.getSafeString("RIVER_CODE");
			String sectionCode = row.getSafeString("RIVER_SECTION");
			for(int m=0;m<time.size();m++) {
				List<Double> ClassList= new ArrayList<Double>();
				String MN = row.getSafeString("MN");
				String stationName = RedisHelper.getInstance().getStationNameByMN(MN);
				String dataText = time.get(m);
				for(int i=0;i<list.size();i++) {
					String mn = list.get(i).getSafeString("MN");
					String type = list.get(i).getSafeString("water_type");
					String ti = list.get(i).getSafeString("DATA_TEXT_");
					double Class=0.0;
					if("I".equals(type)) {
						Class=1;
					} else if ("II".equals(type)) {
						Class=2;
					}else if ("III".equals(type)) {
						Class=3;
					}
					else if ("IV".equals(type)) {
						Class=4;
					}
					else if ("V".equals(type)) {
						Class=5;
					}
					else if ("劣V".equals(type)) {
						Class=6;
					}
					//如果站点以及日期对应上，得出某天某个站点的水质类别
					if(MN.equals(mn) && dataText.equals(ti)) {
						ClassList.add(Class);
					}
				}
				double stationClass=0.0;
				//将污染物水质类别进行升序排序，取最大一个最为站点水质类别
				Collections.sort(ClassList);
				if(ClassList.size()>0) {
					stationClass = ClassList.get(ClassList.size()-1);
				}
				String stationType="";
				if(stationClass==1.0) {
					stationType="I";
				} else if(stationClass==2.0) {
					stationType="II";
				}else if(stationClass==3.0) {
					stationType="III";
				}else if(stationClass==4.0) {
					stationType="IV";
				}else if(stationClass==5.0) {
					stationType="V";
				}else if(stationClass==6.0){
					stationType="劣V";
				} 
				RowBase staType  = new RowBase();
				String stationCode = "";
				stationCode = stationCodes[j];
				staType.setValue("MN", MN);
				staType.setValue("stationName", stationName);
				staType.setValue("date_text",dataText);
				staType.setValue("stationType", stationType);
				staType.setValue("pollCount", mulitPolls.get(stationCode).size());
				staType.setValue("river_code",riverCode);
				staType.setValue("section_code",sectionCode);
				stationWaterType.add(staType);
			}
		}
		return stationWaterType;
	}
	
	/**
	 * 通过污染物的水质类别，以最差的一个作为该站点的水质类别
	 * @param stationid
	 * @param time
	 * @param list
	 * @return
	 */
	private List<RowBase> getPollIndex(List<RowBase> pollIndex,HashMap<String, List<String>> mulitPolls) {
		for(int i=0;i<pollIndex.size();i++) {
			RowBase row = pollIndex.get(i);
			String stationCode = row.getSafeString("STATIONCODE");
			row.setValue("POLLCOUNT", mulitPolls.get(stationCode).size());
		}
		
		return pollIndex;
	}
	
	/**
	 * 获取污染物同比环比报表
	 * gislin
	 * 2016年8月27日
	 * @param endDate 开始时间
	 * @param dateLength 时间长度
	 * @param cycleTypeStr 周期类型 day,week,month,quarter,year
	 * @param isY2Y 是否是同比，同比为ture，环比为flase
	 * @param stationCodes
	 * @param pollCodes
	 * @return
	 * @throws ParseException
	 */
	public JSONObject queryM2MY2YReport(Date endDate,int dateLength,String cycleTypeStr,Boolean isY2Y,String[] stationCodes,HashMap<String,List<String>> mulitPolls) throws ParseException{
		
		String tableName = "";
		
		if ("day".equals(cycleTypeStr)) {
			tableName = "monitor_data_day";
		}else if ("week".equals(cycleTypeStr)) {
			tableName = "monitor_data_week";
		}else if("month".equals(cycleTypeStr)){
			tableName = "monitor_data_month";
		} else if ("quarter".equals(cycleTypeStr)) {
			tableName = "monitor_data_quarter";
		}else if ("year".equals(cycleTypeStr)) {
			tableName = "monitor_data_year";
		}
		
		List<RowBase> riverNames = getMulitPollRiver(stationCodes,mulitPolls);
		List<RowBase> sections = getMulitPollRiverSection(stationCodes,mulitPolls);
		List<RowBase> stationNames = getMulitPollStations(stationCodes,mulitPolls);
		List<RowBase> pollNameList = getMulitPollNames(mulitPolls,stationNames);
		List<String> pollDeduplicated = getPollDeduplicated(pollNameList);
		List<RowBase> pollDeduplicatedList = getPollNames(pollDeduplicated.toArray(new String[pollDeduplicated.size()]));
		
		String[] stationMNs = new String[stationCodes.length];
		//获取mn集合
		for(int i=0;i<stationCodes.length;i++){
			String mn = RedisHelper.getInstance().getMNByStationCode(stationCodes[i]);
			stationMNs[i] = mn;
		}
		
		//获取mn的in where语句和参数
		String mnInWhere = StringUtil.getArrayStrForSql(stationMNs, "mn");
		List<GlobalParameter> pc1 = DataUtil.arrayToParameters(stationMNs, "mn");
		String pollCodeInWhere = StringUtil.getArrayStrForSql(pollDeduplicated.toArray(new String[pollDeduplicated.size()]), "pollCode");
		pc1.addAll(DataUtil.arrayToParameters(pollDeduplicated.toArray(new String[pollDeduplicated.size()]), "pollCode"));
		
		//构造同比或者环比的日期信息
		Y2YM2MDateModel dateModel = new Y2YM2MDateModel(endDate, dateLength, cycleTypeStr, isY2Y);
		List<String> dateTextList = dateModel.getDateTextList();
		
		String dateTexts = "";
		for(String dateText : dateTextList){
			dateTexts += ",'"+dateText+"' P"+dateText;
		}
		dateTexts = dateTexts.replaceFirst(",", "");
		
		//获取数据
		String getDataSql = " select * from( SELECT *\n" +
						"  FROM (select *\n" + 
						"          from (select DATE_TEXT, PARMNAME PARMNAME_,ms.river_code river_code_,ms.river_section river_section_, avg, mn mn_\n" + 
						"                  from "+tableName+" t1\n" + 
						"      					left join BC_COLLECTION_INFO dc on dc.device_num = t1.mn \n"+
						"                        left join BC_SITE_INFO ms on ms.code = dc.site_id \n"+
						"                 where mn in\n" + 
						"                      "+mnInWhere+"\n" + 
						"                   and parmname in "+pollCodeInWhere+") t pivot(sum(avg) for DATE_TEXT in ("+dateTexts+"))) t\n" + 
						" ) t"; 
		
		String forAllDataSql = "";
		for(RowBase station:stationNames){
			String mn = station.getSafeString("MN");
			String stationCode = station.getSafeString("CODE");
			String riverCode = station.getSafeString("RIVER_CODE");
			String riverSection = station.getSafeString("RIVER_SECTION");
			for(String pollCode:pollDeduplicated){
				forAllDataSql += "\n SELECT '"+stationCode+"' STATION_CODE,'"+mn+"' mn,'"+riverCode+"' river_code,'"+riverSection+"' river_section,'"+pollCode+"' parmname,'AVG' value_type FROM DUAL \n"
						+ " union all";
			}
		}
		forAllDataSql = StringUtil.replaceLast(forAllDataSql, "union all", "");
		forAllDataSql = " (SELECT * FROM ("+forAllDataSql+")) t1 ";
		
		getDataSql = getDataSql + " \n right join \n " + forAllDataSql + " on t.mn_ = t1.mn and t.parmname_ = t1.parmname";
		
		pc1.add(new GlobalParameter("startDate", dateModel.getStartDate()));
		pc1.add(new GlobalParameter("endDate", dateModel.getEndDate()));
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("POLLDATA");
		List<RowBase> dataList = DataUtil.getSelectRows(getDataSql, pc1,pTable);
		DecimalFormat df = new DecimalFormat("#0.00");
		
		for(RowBase row : dataList){
			for(int i=1;i<dateTextList.size();i++){
				double baseValue = row.getSafeDouble("P"+dateTextList.get(i-1));
				String dateText = "P"+dateTextList.get(i);
				double value = row.getSafeDouble(dateText);
				if(baseValue == 0 && value == 0){
					row.setValue(dateText+"_PRE", "-");
				}
				else if(baseValue == 0 && value != 0){
					if(row.get("P"+dateTextList.get(i-1)) == null){
						row.setValue(dateText+"_PRE", "-");
					}
					else{
						row.setValue(dateText+"_PRE", "100%");
					}
				}
				else{
					double v = ((value - baseValue) / baseValue) * 100;
					row.setValue(dateText+"_PRE", df.format(v)+"%");
				}
			}
		}
		
		//对数据进行排序
		RowBaseCompare rowBaseComparer = new RowBaseCompare();
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("RIVER_CODE"));
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("RIVER_SECTION"));
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("MN"));
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("PARMNAME"));
		Collections.sort(dataList, rowBaseComparer);
		
		Iterator<RowBase> resultListItera = dataList.iterator();
		while(resultListItera.hasNext()){
			RowBase dataRow = resultListItera.next();
			String stationCode = dataRow.getSafeString("STATION_CODE");
			String curPollCode = dataRow.getSafeString("PARMNAME");
			List<String> curPollList = mulitPolls.get(stationCode);
			if(!curPollList.contains(curPollCode)){
				System.out.println("stationCode:"+stationCode+",curPollCode:"+curPollCode);
				resultListItera.remove();
			}
		}
		
		JSONObject resultJsonObject = new JSONObject();
		resultJsonObject.put("river", riverNames);
		resultJsonObject.put("riverSection", sections);
		resultJsonObject.put("stations", stationNames);
		resultJsonObject.put("polls", pollNameList);
		resultJsonObject.put("dateTexts", dateTextList);
		resultJsonObject.put("data", dataList);
		resultJsonObject.put("pollDeduplicated",pollDeduplicatedList);
		
		return resultJsonObject;
	}
	/**
	 * 水质类别变化结果和综合污染指数变化结果统计
	 */
	private List<RowBase> getWaterTypeAndPollIndexTogether(String[] stationMNs,List<RowBase> waterType,List<RowBase> pollIndex) {
		 List<RowBase> returnResult = new ArrayList<RowBase>();
		for(int j = 0; j < stationMNs.length;j++) {
			String mn = stationMNs[j];
			RowBase combinData = new RowBase();
			for(int i = 0;i < waterType.size();i++) {
				String mns = waterType.get(i).getSafeString("MN");
				String stationName = RedisHelper.getInstance().getStationNameByMN(mns);
				if(mn.equals(mns)) {
					//正式代码
					String Type = waterType.get(i).getSafeString("WATER_TYPE");
					//获取站点污染指数变化结果
					String pollIndexChange = pollIndex.get(i).getSafeString("POLLINDEXCHANGERESULT");
					String poll = pollIndex.get(i).getSafeString("QUALITY");
					String compPollIndex = pollIndex.get(i).getSafeString("POLLINDEX");
					String time = waterType.get(i).getSafeString("DATE_TEXT");
					//数据造假部分，根据poll构造Type
/*					String Type = WaterQualityToWaterType(poll);*/
					time = "P"+time;
					combinData.setValue("MN", mns);
					combinData.setValue("staName", stationName);
					combinData.setValue("DATA_TEXT", time);
					combinData.setValue(time+"WATER_QUALITY_TYPE", Type);
					combinData.setValue(time+"POLL_INDEX", poll);
					combinData.setValue(time+"COMP_POLL_INDEX", compPollIndex);
					combinData.setValue(time+"POLL_INDEX_CHANGE",pollIndexChange);
				}
			}
			returnResult.add(combinData);
		}
		return returnResult;
	}
	
	/**
	 * 根据站点code集合获取河流信息,多站点参数版本
	 * gislin
	 * 2016年8月29日
	 * @param stationCodes
	 * @return
	 */
	private List<RowBase> getMulitPollRiver(String[] stationCodes,HashMap<String,List<String>> mulitPolls){
		//获取in语句
		String stationCodeInWhere = StringUtil.getArrayStrForSql(stationCodes, "stationCode");
		List<GlobalParameter> pc = DataUtil.arrayToParameters(stationCodes, "stationCode");
		
		//获取河流
		String getRivelSql =  " select t1.river_name,t1.river_code,t.code from BC_SITE_INFO t\n" +
						"       left join river_info t1 on t.river_code = t1.river_code "+
						" where  t1.river_name is not null and " +
						" t.code in " + stationCodeInWhere;
		String stationJoinPollSql = getStationPollJoinSql(mulitPolls);
		
		String sql = " SELECT t.river_name,t.river_code,sum(poll_count) poll_count FROM ( \n" +  getRivelSql + " \n )t "
				+ " left join ( " + stationJoinPollSql + ") tt on t.code = tt.station_code \n"
				+"  group by t.river_name,t.river_code order by t.river_code ";
		
		List<RowBase> riverNames = DataUtil.getSelectRows(sql, pc);
		return riverNames;
	}
	
	/**
	 * 根据站点code集合获取河段信息
	 * gislin
	 * 2016年8月29日
	 * @param stationCodes
	 * @return
	 */
	private List<RowBase> getMulitPollRiverSection(String[] stationCodes,HashMap<String,List<String>> mulitPolls){
		
		//获取in语句
		String stationCodeInWhere = StringUtil.getArrayStrForSql(stationCodes, "stationCode");
		List<GlobalParameter> pc = DataUtil.arrayToParameters(stationCodes, "stationCode");
		
		//获取河段
		String getRivelsSctionSql = "select case to_char(t.river_section) when '1' then '上游'\n" +
						"            when '2' then '中游'\n" + 
						"            when '3' then '下游' end section,t.river_section,t.river_code,ri.river_name,t.code \n" + 
						"       from BC_SITE_INFO t left join river_info ri on t.river_code=ri.river_code" +
						"    where t.river_section is not null and"+
						" t.code in "+stationCodeInWhere;
		
		String stationJoinPollSql = getStationPollJoinSql(mulitPolls);
		
		String sql = " SELECT t.section,t.river_code,t.river_section,t.river_code,t.river_name,sum(poll_count) poll_count \n"
				+ " FROM ( \n" +  getRivelsSctionSql + " \n )t "
				+ " left join ( " + stationJoinPollSql + ") tt on t.code = tt.station_code \n"
				+"  group by t.section,t.river_code,t.river_name,t.river_section order by t.river_code,t.section ";
		
		List<RowBase> sections = DataUtil.getSelectRows(sql, pc);
		return sections;
	}
	
	/**
	 * 根据站点code集合获取站点信息
	 * gislin
	 * 2016年8月29日
	 * @param stationCodes
	 * @return
	 */
	private List<RowBase> getMulitPollStations(String[] stationCodes,HashMap<String,List<String>> mulitPolls){

		//获取in语句
		String stationCodeInWhere = StringUtil.getArrayStrForSql(stationCodes, "stationCode");
		List<GlobalParameter> pc = DataUtil.arrayToParameters(stationCodes, "stationCode");
		
		//获取站点
		String getStationNamesSql = "select t.code,t.name,dc.device_num MN,t.RIVER_CODE,t.RIVER_SECTION from BC_SITE_INFO t "
				+ "  left join BC_COLLECTION_INFO dc on dc.site_id = t.code "
				+ " WHERE t.code in "+stationCodeInWhere+" order by t.river_code,t.river_section, dc.device_num " ;
		
		String stationJoinPollSql = getStationPollJoinSql(mulitPolls);
		
		String sql = " SELECT t.code,t.name,t.MN,t.RIVER_CODE,t.RIVER_SECTION,sum(poll_count) poll_count \n"
				+ " FROM ( \n" +  getStationNamesSql + " \n )t "
				+ " left join ( " + stationJoinPollSql + ") tt on t.code = tt.station_code \n"
				+"  group by t.code,t.name,t.MN,t.RIVER_CODE,t.RIVER_SECTION order by t.river_code,t.RIVER_SECTION,t.mn ";
		
		List<RowBase> stationNames = DataUtil.getSelectRows(sql, pc);
		return stationNames;
	}
	
	/**
	 * 根据站点code集合获取河流信息
	 * gislin
	 * 2016年8月29日
	 * @param stationCodes
	 * @return
	 */
	private List<RowBase> getRiver(String[] stationCodes){
		if(stationCodes.length==0) {
			return null;
		}
		//获取in语句
		String stationCodeInWhere = StringUtil.getArrayStrForSql(stationCodes, "stationCode");
		List<GlobalParameter> pc = DataUtil.arrayToParameters(stationCodes, "stationCode");
		
		//获取河流
		String getRivelSql = "select t1.river_name,t1.river_code,count(*) station_count from BC_SITE_INFO t\n" +
						"       left join river_info t1 on t.river_code = t1.river_code "+
						" where  t1.river_name is not null and " +
						" t.code in " + stationCodeInWhere+
						" group by t1.river_name,t1.river_code order by t1.river_code ";
		List<RowBase> riverNames = DataUtil.getSelectRows(getRivelSql, pc);
		return riverNames;
	}
	
	/**
	 * 根据站点code集合获取河段信息
	 * gislin
	 * 2016年8月29日
	 * @param stationCodes
	 * @return
	 */
	private List<RowBase> getRiverSection(String[] stationCodes){
		if(stationCodes.length==0) {
			return null;
		}
		
		//获取in语句
		String stationCodeInWhere = StringUtil.getArrayStrForSql(stationCodes, "stationCode");
		List<GlobalParameter> pc = DataUtil.arrayToParameters(stationCodes, "stationCode");
		
		//获取河段
		String getRivelsSctionSql = "select case to_char(t.river_section) when '1' then '上游'\n" +
						"            when '2' then '中游'\n" + 
						"            when '3' then '下游' end section,t.river_section,t.river_code,ri.river_name,count(*) station_count \n" + 
						"       from BC_SITE_INFO t left join river_info ri on t.river_code=ri.river_code" +
						"    where t.river_section is not null and"+
						" t.code in "+stationCodeInWhere+
						" group by t.river_code,ri.river_name,t.river_section order by river_code ";
		List<RowBase> sections = DataUtil.getSelectRows(getRivelsSctionSql, pc);
		return sections;
	}
	
	/**
	 * 根据站点code集合获取站点信息
	 * gislin
	 * 2016年8月29日
	 * @param stationCodes
	 * @return
	 */
	private List<RowBase> getStations(String[] stationCodes){
		if(stationCodes.length==0) {
			return null;
		}
		//获取in语句
		String stationCodeInWhere = StringUtil.getArrayStrForSql(stationCodes, "stationCode");
		List<GlobalParameter> pc = DataUtil.arrayToParameters(stationCodes, "stationCode");
		
		//获取站点
		String getStationNamesSql = "select t.code,t.name,dc.device_num MN,t.RIVER_CODE,t.RIVER_SECTION from BC_SITE_INFO t "
				+ "  left join BC_COLLECTION_INFO dc on dc.site_id = t.code "
				+ " WHERE t.code in "+stationCodeInWhere+" order by t.river_code,t.river_section, dc.device_num " ;
		List<RowBase> stationNames = DataUtil.getSelectRows(getStationNamesSql, pc);
		return stationNames;
	}
	
	/**
	 * 根据参数code数组翻译并排序
	 * gislin
	 * 2016年8月29日
	 * @param pollCodes
	 * @return
	 */
	private List<RowBase> getPollNames(String[] pollCodes){
		//获取污染物
		List<RowBase> pollNameList = new ArrayList<>();
		List<String> pollCodeList = new ArrayList<>();
		if(!StringUtils.IsNullOrEmpty(pollCodes)) {
			pollCodeList= Arrays.asList(pollCodes);
		} else {
			return pollNameList;
		}
		//排序,为了跟上面的数据一直
		Collections.sort(pollCodeList, new PollCodeCompare());
		for(int i=0;i<pollCodeList.size();i++){
			RowBase row = new RowBase();
			String pollCode = pollCodeList.get(i);
			String pollName = RedisHelper.getInstance().translationDictValue("MONITOR_PARAM", pollCode);
			String pollUnit = RedisHelper.getInstance().translationDictUnit("MONITOR_PARAM", pollCode);
			row.setValue("pollName", pollName);
			row.setValue("pollUnit", pollUnit);
			row.setValue("pollCode", pollCode);
			
			if(!StringUtil.isEmpty(pollUnit)){
				row.setValue("pollNameShow", pollName+"("+pollUnit+")");
			}
			else{
				row.setValue("pollNameShow", pollName);
			}
			
			pollNameList.add(row);
		}
		return pollNameList;
	}
	
	/**
	 * 根据参数code数组翻译并排序
	 * @param mulitPolls
	 * @return
	 */
	private List<RowBase> getMulitPollNames(HashMap<String,List<String>> mulitPolls,List<RowBase> stationList){
		
		List<RowBase> pollNameList = new ArrayList<>();
		for(RowBase stationRow:stationList){
			String stationCode = stationRow.getSafeString("CODE");
			List<String> pollCodeList = mulitPolls.get(stationCode);
			
			//排序,为了跟上面的数据一直
			Collections.sort(pollCodeList, new PollCodeCompare());
			for(int i=0;i<pollCodeList.size();i++){
				RowBase row = new RowBase();
				String pollCode = pollCodeList.get(i);
				String pollName = RedisHelper.getInstance().translationDictValue("MONITOR_PARAM", pollCode);
				String pollUnit = RedisHelper.getInstance().translationDictUnit("MONITOR_PARAM", pollCode);
				row.setValue("pollName", pollName);
				row.setValue("pollUnit", pollUnit);
				row.setValue("pollCode", pollCode);
				
				if(!StringUtil.isEmpty(pollUnit)){
					row.setValue("pollNameShow", pollName+"("+pollUnit+")");
				}
				else{
					row.setValue("pollNameShow", pollName);
				}
				
				pollNameList.add(row);
			}
		}
		return pollNameList;
	}	
	/**
	 * 水质类别分析中的污染物获取
	 * 根据参数code数组翻译并排序
	 * @param mulitPolls
	 * @return
	 */
	private List<RowBase> getPollNames(HashMap<String,List<String>> mulitPolls,List<RowBase> stationList,List<RowBase> dateTextList){
		
		List<RowBase> pollNameLists = new ArrayList<>();
		for(RowBase stationRow:stationList){
			List<RowBase> pollNameList = new ArrayList<>();
			String stationCode = stationRow.getSafeString("CODE");
			List<String> pollCodeList = mulitPolls.get(stationCode);
			
			//排序,为了跟上面的数据一直
			Collections.sort(pollCodeList, new PollCodeCompare());
			for(int i=0;i<pollCodeList.size();i++){
				RowBase row = new RowBase();
				String pollCode = pollCodeList.get(i);
				String pollName = RedisHelper.getInstance().translationDictValue("MONITOR_PARAM", pollCode);
				String pollUnit = RedisHelper.getInstance().translationDictUnit("MONITOR_PARAM", pollCode);
				row.setValue("pollName", pollName);
				row.setValue("pollUnit", pollUnit);
				row.setValue("pollCode", pollCode);
				
				if(!StringUtil.isEmpty(pollUnit)){
					row.setValue("pollNameShow", pollName+"("+pollUnit+")");
				}
				else{
					row.setValue("pollNameShow", pollName);
				}
				
				pollNameList.add(row);
			}
			for (RowBase row:dateTextList) {
				for(int k=0;k<pollNameList.size();k++) {
					RowBase r = new RowBase();
					r=pollNameList.get(k);
					pollNameLists.add(r);
				}
			}
		}
		return pollNameLists;
	}
	
	/**
	 * 获取多站点参数的连接sql
	 * @param mulitPolls
	 * @return
	 */
	private String getStationPollJoinSql(HashMap<String,List<String>> mulitPolls){
		
		String sql = "";
		for(Entry<String, List<String>> entry:mulitPolls.entrySet()){
			String stationCode = entry.getKey();
			sql += "\n SELECT '"+stationCode+"' station_code,"+entry.getValue().size()+" poll_count from dual union all";
		}
		sql = StringUtil.replaceLast(sql, "union all", "");
		return sql;
	}
	
	/**
	 * 把参数去重
	 * @param pollList
	 * @return
	 */
	private List<String> getPollDeduplicated(List<RowBase> pollList){
		Set<String> pollSet = new HashSet<>();
		
		for(RowBase poll : pollList){
			pollSet.add(poll.getSafeString("pollCode"));
		}
		List<String> result = new ArrayList<>(pollSet);
		return result;
	}
	
	/**
	 * 获取水质类别，综合污染指数同比环比报表
	 * gislin
	 * 2016年8月27日
	 * @param startDate 开始时间
	 * @param dateLength 时间长度
	 * @param cycleTypeStr 周期类型 day,week,month,quarter,year
	 * @param isY2Y 是否是同比，同比为ture，环比为flase
	 * @param stationCodes
	 * @param pollCodes
	 * @return
	 * @throws ParseException
	 */
	public HashMap<String, Object> queryWaterTypePollIndexM2MY2YReport(Date startDate,int dateLength,String cycleTypeStr,Boolean isY2Y,String[] stationCodes,String[] pollCodes) throws ParseException{
		
		String tableName = "";
		
		if ("day".equals(cycleTypeStr)) {
			tableName = "monitor_data_day";
		}else if ("week".equals(cycleTypeStr)) {
			tableName = "monitor_data_week";
		}else if("month".equals(cycleTypeStr)){
			tableName = "monitor_data_month";
		} else if ("quarter".equals(cycleTypeStr)) {
			tableName = "monitor_data_quarter";
		}else if ("year".equals(cycleTypeStr)) {
			tableName = "monitor_data_year";
		}
		
		//获取河流
		List<RowBase> riverNames = getRiver(stationCodes);
		List<RowBase> sections = getRiverSection(stationCodes);
		List<RowBase> stationNames = getStations(stationCodes);
		
		
		
		String[] stationMNs = new String[stationCodes.length];
		//获取mn集合
		for(int i=0;i<stationCodes.length;i++){
			String mn = RedisHelper.getInstance().getMNByStationCode(stationCodes[i]);
			stationMNs[i] = mn;
		}
		
		//获取mn的in where语句和参数
		String mnInWhere = StringUtil.getArrayStrForSql(stationMNs, "mn");
		List<GlobalParameter> pc1 = DataUtil.arrayToParameters(stationMNs, "mn");
		pc1.addAll(DataUtil.arrayToParameters(pollCodes, "pollCode"));
		
		//构造同比或者环比的日期信息
		Y2YM2MDateModel dateModel = new Y2YM2MDateModel(startDate, dateLength, cycleTypeStr, isY2Y);
		List<String> dateTextList = dateModel.getDateTextList();
		
		String dateTexts = "";
		for(String dateText : dateTextList){
			dateTexts += ",'"+dateText+"'";
		}
		dateTexts = dateTexts.replaceFirst(",", "");
		

		pc1.add(new GlobalParameter("startDate", dateModel.getStartDate()));
		pc1.add(new GlobalParameter("endDate", dateModel.getEndDate()));
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("POLLDATA");
		
		String labelName="";
		if("day".equals(cycleTypeStr)) {
			tableName = "day_water_type";
			labelName="（天）";
		}else if("month".equals(cycleTypeStr)){
			tableName = "month_water_type";
			labelName="（月）";
		}else if("week".equals(cycleTypeStr)) {
			tableName = "week_water_type";
			labelName="（周）";
		}else if("quarter".equals(cycleTypeStr)) {
			tableName = "quarter_water_type";
			labelName="（季度）";
		}else if("year".equals(cycleTypeStr)) {
			tableName = "year_water_type";
			labelName="（年）";
		}
				//查询站点水质类别项SQL
				String sql = "select * from ( select ms.river_code,ms.river_section,mn mn_,data_text data_text_,parmname parmname_,water_type  from "+tableName+" t"
						+ " left join BC_COLLECTION_INFO dc on dc.device_num = t.mn \n"+
						"   left join BC_SITE_INFO ms on ms.code = dc.site_id \n"+
						" where 1=1 " +
						"  and parmname = '"+"STATION_WATER_TYPE"+"'"+
						" and datatime >= @startDate\n" + 
						" and datatime <= @endDate\n" + 
						" and mn in "+mnInWhere+"\n" +
						" and DATA_TEXT in ("+dateTexts+")"+
						" ) t  ";
				
				String forAllDataSql = "";
				for(int k =0;k<stationNames.size();k++){
					RowBase station = stationNames.get(k);
					String mn = station.getSafeString("MN");
					String riverCode = station.getSafeString("RIVER_CODE");
					String riverSection = station.getSafeString("RIVER_SECTION");
						String pollCode = "STATION_WATER_TYPE";
						for(int i=0;i< dateTextList.size();i++) {
							String date = dateTextList.get(i);
							int nn = i+k*dateTextList.size();
							forAllDataSql += "\n SELECT '"+mn+"' mn,'"+riverCode+"' river_code,'"+riverSection+"' river_section,'"+pollCode+"' parmname,'AVG' value_type,'"+date+"' date_text,'"+nn+"' Num FROM DUAL \n"
									+ " union all";
						}
				}
				forAllDataSql = StringUtil.replaceLast(forAllDataSql, "union all", "");
				forAllDataSql = " (SELECT * FROM ("+forAllDataSql+")) t1 ";
				
				sql = sql + " \n right join \n " + forAllDataSql + " on t.mn_ = t1.mn and t.parmname_ = t1.parmname and t.data_text_=t1.date_text ORDER BY t1.river_code,t1.river_section,t1.mn,t1.date_text,T1.parmname";

				//查询站点水质类别信息
				List<RowBase> typelist = DataUtil.getSelectRows(sql,pc1, pTable);
				//地表水
				Map<String,String> params = new HashMap<>();
				params.put("category", "SURFACE_WATER");
				for(int i=0;i<typelist.size();i++) {
					String parmname="";
					parmname = typelist.get(i).getSafeString("parmname");
					String Names = RedisHelper.getInstance().translationDictValue("MONITOR_PARAM", parmname);
					typelist.get(i).setValue("parm_name", Names);
				}
				//获取综合污染指数
				if("day".equals(cycleTypeStr)) {
					tableName = "monitor_data_day";
				}else if("week".equals(cycleTypeStr)){
					tableName = "monitor_data_week";
				}else if("month".equals(cycleTypeStr)) {
					tableName = "monitor_data_month";
				}else if("quarter".equals(cycleTypeStr)) {
					tableName = "monitor_data_quarter";
				}else if("year".equals(cycleTypeStr)) {
					tableName = "monitor_data_year";
				}
				String comPollIndexSql = "select * from (SELECT ms.river_code,ms.river_section,mn mn_,date_text date_text_,parmname parmname_,avg from "+tableName+ " t " 
						+ " left join BC_COLLECTION_INFO dc on dc.device_num = t.mn \n"+
						"   left join BC_SITE_INFO ms on ms.code = dc.site_id \n"+
						" where 1=1 " +
						" and parmname='COMP_POLL_INDEX'" +
						" and datatime >= @startDate\n" + 
						" and datatime <= @endDate\n" + 
						"  and mn in "+mnInWhere +
						" and  DATE_TEXT in ("+dateTexts+") \n"+
						"order by river_code, river_section, mn,datatime) t ";
				
				String selectDataSql = "";
				for(int k =0;k<stationNames.size();k++){
					RowBase station = stationNames.get(k);
					String mn = station.getSafeString("MN");
					String riverCode = station.getSafeString("RIVER_CODE");
					String riverSection = station.getSafeString("RIVER_SECTION");
						String pollCode = "COMP_POLL_INDEX";
						for (int i =0;i<dateTextList.size();i++) {
							String date = dateTextList.get(i);
							int nn = i+k*dateTextList.size();
							selectDataSql += "\n SELECT '"+mn+"' mn,'"+riverCode+"' river_code,'"+riverSection+"' river_section,'"+pollCode+"' parmname,'AVG' value_type,'"+date+"' date_text,'"+nn+"' Num  FROM DUAL \n"
									+ " union all";
						}
				}
				selectDataSql = StringUtil.replaceLast(selectDataSql, "union all", "");
				selectDataSql = " (SELECT * FROM ("+selectDataSql+")) t1 ";
				 //通过综合污染指数判断是几类水质，然后得出结果：好，较好，轻度污染，中度污染，严重污染。
				comPollIndexSql = comPollIndexSql + " \n right join \n " + selectDataSql + " on  t.mn_ = t1.mn  and t.date_text_=t1.date_text and t.parmname_=t1.parmname ORDER BY t1.river_code,t1.river_section,t1.mn,t1.date_text,T1.parmname ";

				 List<RowBase> comPollIndexresult = DataUtil.getSelectRows(comPollIndexSql,pc1, null);
				 int looptimes = comPollIndexresult.size()/stationMNs.length;
				 for(int k =0;k < stationNames.size();k++) {
					 String MN = stationNames.get(k).getSafeString("MN");
					 for(int i=0;i<comPollIndexresult.size();i++) {
						 double avgindex = comPollIndexresult.get(i).getSafeDouble("avg");
						 String mn = comPollIndexresult.get(i).getSafeString("MN");
						 double first=0.0;
						 double nextIndex = 0.0;
						 String PollIndexChangeResult ="";
						 if(MN.equals(mn)) {
							 //如果result只有一个值或者是第一个值时没有对比就没有变化的结果，PollIndexChangeResult=""
							 //=其中，针对多站点情况，每个站点的第一个日期的变化率都为"",
							 if(comPollIndexresult.size()==1||(i%looptimes)==0) {
								 PollIndexChangeResult="";
							 } else {
								 first = comPollIndexresult.get(i-1).getSafeDouble("avg");
								 nextIndex = comPollIndexresult.get(i).getSafeDouble("avg");
								 PollIndexChangeResult = getPollIndexChangeResult(first,nextIndex);
							 }
							 //计算综合污染指数。
							 String quality="";
							 quality=getPollIndexResult(avgindex);
							 comPollIndexresult.get(i).setValue("pollIndex", avgindex);
							 comPollIndexresult.get(i).setValue("quality", quality);
							 comPollIndexresult.get(i).setValue("PollIndexChangeResult", PollIndexChangeResult);
						 }
					 }
				 }
		 //分别统计水质类别变化结果和综合污染指数变化结果
		 List<RowBase> combinResult = getWaterTypeAndPollIndexTogether(stationMNs,typelist,comPollIndexresult); 
		HashMap<String, Object> resultMapObject = new HashMap<>();
		resultMapObject.put("river", riverNames);
		resultMapObject.put("riverSection", sections);
		resultMapObject.put("stations", stationNames);
		resultMapObject.put("dateTexts", dateTextList);
		resultMapObject.put("comparePart",combinResult);
		resultMapObject.put("labelName",labelName);
		
		return resultMapObject;
	}
	/**
	 * 数据造假，根据水质类别构造水质状况。
	 */
	private String WaterQualityToWaterType(String waterQuality) {
		if("好".equals(waterQuality)) {
			return "I";
		} else if("较好".equals(waterQuality)) {
			return "II";
		}else if("轻度污染".equals(waterQuality)) {
			return "III";
		}else if("中度污染".equals(waterQuality)) {
			return "IV";
		}else if("重污染".equals(waterQuality)) {
			return "V";
		}else if("严重污染".equals(waterQuality)) {
			return "劣V";
		}
		return "";
	}
	/**
	 * 通过站点水质类别，以最差的站点水质类别最为该河段的水质类别
	 * @param stationid
	 * @param time
	 * @param list
	 * @return
	 */
	private List<RowBase> getRiverSectionType(List<RowBase> sections,List<RowBase> stationType) {
		
		for(int k = 0; k< sections.size(); k++) {
			List<Double> ClassList= new ArrayList<Double>();
			RowBase row = new RowBase();
			row=sections.get(k);
			String rivercode = row.getSafeString("RIVER_CODE");
			String sectioncode = row.getSafeString("RIVER_SECTION");
			for(int i =0;i < stationType.size();i++) {
				RowBase r = stationType.get(i);
				
				String river = r.getSafeString("RIVER_CODE");
				String section = r.getSafeString("SECTION_CODE");
				double Class=0.0;
				if(rivercode.equals(river)&&section.equals(sectioncode)) {
					String staType = stationType.get(i).getSafeString("stationType");
					if("I".equals(staType)) {
						Class=1;
					} else if ("II".equals(staType)) {
						Class=2;
					}else if ("III".equals(staType)) {
						Class=3;
					}
					else if ("IV".equals(staType)) {
						Class=4;
					}
					else if ("V".equals(staType)) {
						Class=5;
					}
					else if ("劣V".equals(staType)) {
						Class=6;
					}
					ClassList.add(Class);
				}
			}
			//将污染物水质类别进行升序排序，取最大一个最为站点水质类别
			Collections.sort(ClassList);
			double stationClass=0.0;
			if(ClassList.size()>0) {
				stationClass = ClassList.get(ClassList.size()-1);
			}
			String Type="";
			if(stationClass==1.0) {
				Type="I";
			} else if(stationClass==2.0) {
				Type="II";
			}else if(stationClass==3.0) {
				Type="III";
			}else if(stationClass==4.0) {
				Type="IV";
			}else if(stationClass==5.0) {
				Type="V";
			}else if(stationClass==6.0){
				Type="劣V";
			} 
			sections.get(k).setValue("sectionType", Type);
		}
		return sections;
	}
	
	/**
	 * 通过比较该河流段的所有站点的水质状况，以最差的站点作为该河段的水质状况
	 * @param stationid
	 * @param time
	 * @param list
	 * @return
	 */
	private List<RowBase> getRiverSectionPollIndex(List<RowBase> stationNames,List<RowBase> sections,List<RowBase> pollIndex) {
		
		for(int m=0;m<sections.size();m++) {
		    String riverCode = sections.get(m).getSafeString("RIVER_CODE");
			String riverSection = sections.get(m).getSafeString("RIVER_SECTION");
			List<Double> ClassList= new ArrayList<Double>();
				for(int k=0;k<stationNames.size();k++) {
					String code = stationNames.get(k).getSafeString("RIVER_CODE");
					String section = stationNames.get(k).getSafeString("RIVER_SECTION");
					String mn = stationNames.get(k).getSafeString("MN");
					if(riverCode.equals(code)&& riverSection.equals(section)) {
						double qualityClass=0.0;
						for(int i=0;i<pollIndex.size();i++) {
							String MN = pollIndex.get(i).getSafeString("MN");
							String quality = pollIndex.get(i).getSafeString("QUALITY");
							if(mn.equals(MN)) {
								if(quality!=null&&!"".equals(quality)) {
									if("好".equals(quality)) {
										qualityClass=1.0;
									} else if("较好".equals(quality)) {
										qualityClass=2.0;
									} else if("轻度污染".equals(quality)) {
										qualityClass=3.0;
									} else if("中度污染".equals(quality)) {
										qualityClass=4.0;
									} else if("重污染".equals(quality)) {
										qualityClass=5.0;
									} else if("严重污染".equals(quality)) {
										qualityClass=6.0;
									}
								}
								ClassList.add(qualityClass);
							}
						}
					}
				}
				Collections.sort(ClassList);
				double sectionPollIndex=0.0;
				if(ClassList.size()>0) {
					sectionPollIndex = ClassList.get(ClassList.size()-1);
				}
				String Type="";
				if(sectionPollIndex==1.0) {
					Type="好";
				} else if(sectionPollIndex==2.0) {
					Type="较好";
				}else if(sectionPollIndex==3.0) {
					Type="轻度污染";
				}else if(sectionPollIndex==4.0) {
					Type="中度污染";
				}else if(sectionPollIndex==5.0) {
					Type="重污染";
				}else if(sectionPollIndex==6.0){
					Type="严重污染";
				}
			sections.get(m).setValue("sectionPollIndex", Type);
		}
	return sections;
	}
	
/**
 * 削减量查询
 * 
 * @param startDate
 * @param endDate
 * @param cycleTypeStr
 * @param stationCodes
 * @param snStationCode
 * @param pollCodes
 * @return
 * @throws ParseException 
 */
	public JSONObject queryReductData(Date startDate,Date endDate,String cycleTypeStr,String[] GXStationCodes,String SNStationCode,String[] pollCodes,String reductMethod, String excuteStadard) throws ParseException{
		String[] SNdata = SNStationCode.split(",");
		HashMap<String,Object> SNReductData = queryPollReduction(startDate, endDate, cycleTypeStr, SNdata, pollCodes,reductMethod,excuteStadard);
		HashMap<String,Object> GXReductData = queryPollReduction(startDate, endDate, cycleTypeStr, GXStationCodes, pollCodes,reductMethod,excuteStadard);
		
		JSONObject resultObject = new JSONObject();
		resultObject.put("SNReductData", SNReductData);
		resultObject.put("GXReductData", GXReductData);
		return resultObject;
	}
	/**
	 * 查询站点削减量方法
	 * @param startDate
	 * @param endDate
	 * @param cycleTypeStr
	 * @param stationCodes
	 * @param snStationCode
	 * @param pollCodes
	 * @return
	 * @throws ParseException 
	 */
	private HashMap<String,Object> queryPollReduction(Date startDate,Date endDate,String cycleTypeStr,String[] stationCode,String[] pollCodes,String reductMethod, String excuteStandard) throws ParseException {
		HashMap<String,Object> result = new HashMap<>();
		if(stationCode.length==0||(stationCode.length==1&&"".equals(stationCode[0]))) {
			return null;
		}
		if(pollCodes==null ||pollCodes.length==0) {
			return null;
		}
		String[] stationCodes = new String[stationCode.length];
		String[] sectionArray = new String[stationCode.length];
		for(int i=0;i<stationCode.length;i++) {
			stationCodes[i] = stationCode[i].split(":")[0];
			sectionArray[i] = stationCode[i].split(":")[1];
		}
		//对贡献站点和受纳站点做了处理，分别根据他们所在的河段采用不同级别的最高浓度值，上游采用LV5级别，中游LV4，下游LV3
		Arrays.sort(sectionArray);
		if(sectionArray.length>0) {
			if("1".equals(sectionArray[sectionArray.length-1])) {
				excuteStandard="LV3";
			} else if("2".equals(sectionArray[sectionArray.length-1])) {
				excuteStandard="LV4";
			} else if("3".equals(sectionArray[sectionArray.length-1])) {
				excuteStandard="LV5";
			}
		}
		
		String tableName = "";
		String subSql = "";
		if ("day".equals(cycleTypeStr)) {
			tableName = "monitor_data_day";
		}else if ("week".equals(cycleTypeStr)) {
			tableName = "monitor_data_week";
		}else if("month".equals(cycleTypeStr)){
			tableName = "monitor_data_month";
		} else if ("quarter".equals(cycleTypeStr)) {
			tableName = "monitor_data_quarter";
		}else if ("year".equals(cycleTypeStr)) {
			tableName = "monitor_data_year";
		}
		
		if(!StringUtils.IsNullOrEmpty(excuteStandard)) {
			subSql=" and t.excute_standard='"+excuteStandard+"'\n";
		}
		List<RowBase> riverNames = getRiver(stationCodes);
		List<RowBase> sections = getRiverSection(stationCodes);
		List<RowBase> stationNames = getStations(stationCodes);
		List<RowBase> pollNameList = getPollNames(pollCodes);
		
		String[] stationMNs = new String[stationCodes.length];
		//获取mn集合
		for(int i=0;i<stationCodes.length;i++){
			String mn = RedisHelper.getInstance().getMNByStationCode(stationCodes[i]);
			stationMNs[i] = mn;
		}
		//获取mn的in where语句和参数
		String mnInWhere = StringUtil.getArrayStrForSql(stationMNs, "mn");
		List<GlobalParameter> pc1 = DataUtil.arrayToParameters(stationMNs, "mn");
		String pollCodeInWhere = StringUtil.getArrayStrForSql(pollCodes, "pollCode");
		pc1.addAll(DataUtil.arrayToParameters(pollCodes, "pollCode"));
		pc1.add(new GlobalParameter("startDate", startDate));
		pc1.add(new GlobalParameter("endDate", endDate));
		
		String dateTextsql = " select distinct trunc(datatime) time ,DATE_TEXT FROM "+tableName+" WHERE DATATIME >= @startDate and DATATIME <= @endDate "+
				 " and mn in "+mnInWhere +
				 " and parmname in "+pollCodeInWhere+" order by time,DATE_TEXT asc";
		
		List<String> dateTextList = DataUtil.getSelectStringRows(dateTextsql, pc1, "DATE_TEXT");
		//如果数据库里没有数据，查询的日期将为空，影响前端的展示，通过调用同比环比Model生成日期。
		if(dateTextList.size()==0&&"day".equals(cycleTypeStr)) {
			int length = (int)((endDate.getTime() - startDate.getTime())/86400000)+1;
			Y2YM2MDateModel dateModel = new Y2YM2MDateModel(endDate, length, cycleTypeStr, false);
			dateTextList = dateModel.getDateTextList();
		}
		//查询出各个污染物对应的最高的排放浓度值（mg/L）
		String queryMaxEmisSql = " select t.poll_code,t.max_emission_value from poll_max_emission t where 1=1 \n"
								+" and t.poll_code in "+pollCodeInWhere+subSql+"order by poll_code";

		
		String queryConcentSql = "  select t.mn,t.parmname, t.date_text, t.avg  from "+ tableName+" t where 1=1"
								+" and t.parmname in "+pollCodeInWhere
								+" and t.datatime>=@startDate and t.datatime<=@endDate \n"
								+" and mn in "+mnInWhere 
								+"  group by t.mn,t.date_text,t.parmname,t.avg   order by t.mn,t.parmname,t.date_text ";

		//查询查询时间段每天的累计流量，用于计算每天的累计排放量
		String queryEmissionSql = "  select t.mn, t.date_text, t.avg  from "+tableName +" t where t.parmname='B01TOTAL'\n"
								+"  and t.datatime>=@startDate and t.datatime<=@endDate \n"
								+" and mn in "+mnInWhere 
								+"  group by t.mn,t.date_text,t.avg   order by t.mn,t.date_text ";
		
		String forAllDataSql = "";
		for(RowBase station:stationNames){
			String mn = station.getSafeString("MN");
			for(int i=0;i<dateTextList.size();i++){
				String date = dateTextList.get(i);
				forAllDataSql += "\n SELECT '"+mn+"' mn,'"+date+"' DATE_TEXT"+ " FROM DUAL \n"
								+" union all";
			}
		}
		forAllDataSql = StringUtil.replaceLast(forAllDataSql, "union all", "");
		forAllDataSql = " (SELECT * FROM ("+forAllDataSql+")) t1 ";
		if(!" (SELECT * FROM ()) t1 ".equals(forAllDataSql)) {
			queryEmissionSql = "select tt.avg,t1.mn,t1.date_text from (" +queryEmissionSql+") tt " + " \n right join \n " + forAllDataSql + " on tt.mn = t1.mn and tt.date_text = t1.DATE_TEXT  order by t1.mn,date_text";
		}
		List<GlobalParameter> pc = new ArrayList<GlobalParameter>();
		pc.add(new GlobalParameter("startDate", startDate));
		pc.add(new GlobalParameter("endDate", endDate));
		List<RowBase> pollEmission =  DataUtil.getSelectRows(queryEmissionSql, pc1);
		List<RowBase> maxConcentValue =  DataUtil.getSelectRows(queryMaxEmisSql, pc1);
		List<RowBase> realConcentValue =  DataUtil.getSelectRows(queryConcentSql, pc1);
		if("percent".equals(reductMethod)) {
			//允许最大的排放量
			List<RowBase> MaxEmissionResults = computeMaxEmissonAmount(maxConcentValue,pollEmission);
			//实际的排放量
			List<RowBase> RealEmissionResults = computeRealEmissonAmount(realConcentValue,pollEmission);
			//削减量和削减率
			List<RowBase> ReductValueAndRate = computeReductAndRate(MaxEmissionResults,RealEmissionResults);
			//综合最大排量，实际排量，削减量和削减率
			List<RowBase> ReductResult = getTotherMaxAndRealEmission(stationNames,pollCodes,MaxEmissionResults,RealEmissionResults,ReductValueAndRate);
			for(RowBase row:ReductResult){
				for(String dateText : dateTextList){
					String MAXEMISSION = row.getSafeString("MAXEMISSION"+dateText);
					String REALEMISSION = row.getSafeString("REALEMISSION"+dateText);
					String REDUCTVALUE = row.getSafeString("REDUCTVALUE"+dateText);
					String REACUCTRATE = row.getSafeString("REACUCTRATE"+dateText);
					if(MAXEMISSION == null){
						row.setValue("MAXEMISSION"+dateText, "-");
					}
					if(REALEMISSION == null){
						row.setValue("REALEMISSION"+dateText, "-");
					}
					if(REDUCTVALUE == null){
						row.setValue("REDUCTVALUE"+dateText, "-");
					}
					if(REACUCTRATE == null){
						row.setValue("REACUCTRATE"+dateText, "-");
					}
				}
			}
			result.put("dateTextList",dateTextList);
			result.put("riverNames",riverNames);
			result.put("sections",sections);
			result.put("stationNames",stationNames);
			result.put("pollNameList",pollNameList);
			
			result.put("ReductResult",ReductResult);
		}
		return result;
	}
	/**
	 * 计算最大允许排放量
	 * @param maxConcent,最大浓度值
	 * @param pollEmission，累计流量
	 * @return
	 */
	private List<RowBase> computeMaxEmissonAmount(List<RowBase>maxConcent,List<RowBase>pollEmission ) {
		List<RowBase> result = new ArrayList<RowBase>();
		DecimalFormat df = new DecimalFormat("#.00");
		for(int j=0;j<maxConcent.size();j++) {
			RowBase maxtRow = maxConcent.get(j);
			String pollCode = maxtRow.getSafeString("POLL_CODE");
			double maxValue = maxtRow.getSafeDouble("MAX_EMISSION_VALUE");
			for(int i=0;i<pollEmission.size();i++) {
				RowBase totalResult = new RowBase();
				RowBase row = pollEmission.get(i);
				//某一天的累计流量
				double B01Total = row.getSafeDouble("AVG");
				String date_text = row.getSafeString("DATE_TEXT");
				String mn = row.getSafeString("MN");
				String total = df.format(B01Total*maxValue/1000000.0);
				if(total.equals(".00")) {
					total="0.00";
				}
				totalResult.setValue("mn", mn);
				totalResult.setValue("date_text", date_text);
				totalResult.setValue("pollCode", pollCode);
				totalResult.setValue("maxEmission"+date_text, total);
				result.add(totalResult);
			}
		}
		return result;
	}
	/**
	 * 计算实际排放量
	 * @param realConcent
	 * @param pollEmission
	 * @return
	 */
	private List<RowBase> computeRealEmissonAmount(List<RowBase>realConcent,List<RowBase>pollEmission ) {
		List<RowBase> result = new ArrayList<RowBase>();
		DecimalFormat df = new DecimalFormat("#.00");
		for(int i=0;i<pollEmission.size();i++) {
			RowBase row = pollEmission.get(i);
			//某一天的累计流量
			double B01Total = row.getSafeDouble("avg");
			String mn1 = row.getSafeString("MN");
			String date_text = row.getSafeString("date_text");
			for(int j=0;j<realConcent.size();j++) {
				RowBase contentRow = realConcent.get(j);
				String mn = contentRow.getSafeString("mn");
				String dateText = contentRow.getSafeString("DATE_TEXT");
				String pollCode = contentRow.getSafeString("PARMNAME");
				if(mn1.equals(mn) && date_text.equals(dateText)) {
					RowBase totalResult = new RowBase();
					double avgValue = contentRow.getSafeDouble("avg");
					String total = df.format(B01Total*avgValue/1000000.0);
					if(total.equals(".00")) {
						total="0.00";
					}
					
					totalResult.setValue("mn", mn);
					totalResult.setValue("date_text", date_text);
					totalResult.setValue("pollCode", pollCode);
					totalResult.setValue("realEmission"+date_text, total);
					result.add(totalResult);
				}
			}
		}
		return result;
	} 
	/**
	 * 计算削减量以及削减率
	 * @param maxEmission
	 * @param RealEmission
	 * @return
	 */
	
	private List<RowBase> computeReductAndRate(List<RowBase>maxEmission,List<RowBase>RealEmission ) {
		List<RowBase> result = new ArrayList<RowBase>();
		for(int i=0;i<maxEmission.size();i++) {
			RowBase rltRow = new RowBase();
			RowBase row = maxEmission.get(i);
			String dateText = row.getSafeString("DATE_TEXT");
			double maxValue = row.getSafeDouble("MAXEMISSION"+dateText);
			String mn = row.getSafeString("mn");
			String pollCode = row.getSafeString("POLLCODE");
			for(int j=0;j<RealEmission.size();j++) {
				RowBase realrow = RealEmission.get(j);
				String date = realrow.getSafeString("DATE_TEXT");
				double realValue = realrow.getSafeDouble("REALEMISSION"+date);
				String realMn = realrow.getSafeString("mn");
				String realpollCode = realrow.getSafeString("POLLCODE");
				if(mn.equals(realMn) && dateText.equals(date)&&pollCode.equals(realpollCode)) {
					double ReductValue = realValue-maxValue;
					String ReacuctRate = String.format("%.2f", (ReductValue/realValue)*100)+"%";
					if(ReductValue!=0.0&&realValue==0.0) {
						ReacuctRate="∞%";
					}
					if(ReductValue==0.0&&realValue==0.0) {
						ReacuctRate="0%";
					}
					rltRow.setValue("mn", mn);
					rltRow.setValue("pollCode", pollCode);
					rltRow.setValue("dateText", dateText);
					rltRow.setValue("ReductValue"+dateText, ReductValue);
					rltRow.setValue("ReacuctRate"+dateText, ReacuctRate);
					result.add(rltRow);
				}
			}
		}
		return result;
	} 
	
	private List<RowBase> getTotherMaxAndRealEmission(List<RowBase> stationMNs,String[] pollCodes,List<RowBase>maxEmission,List<RowBase>RealEmission,List<RowBase> ReductValueAndRate) {
		List<RowBase> result = new ArrayList<>();
		for(int k=0;k<stationMNs.size();k++) {
			RowBase stationRow = new RowBase();
			stationRow = stationMNs.get(k);
			String mn=stationRow.getSafeString("MN");
				for( int kk=0;kk<pollCodes.length;kk++) {
					String pollcode = pollCodes[kk];
					RowBase rstRow = new RowBase();
					rstRow.setValue("MN", mn);
					for(int i=0;i<maxEmission.size();i++) {
						RowBase mxRow = maxEmission.get(i);
						String maxMN = mxRow.getSafeString("MN");
						String pollCode = mxRow.getSafeString("POLLCODE");
						if(mn.equals(maxMN)&&pollcode.equals(pollCode)) {
							String maxDateText = mxRow.getSafeString("DATE_TEXT");
							String MAXEMISSION = mxRow.getSafeString("MAXEMISSION"+maxDateText);
							rstRow.setValue("MAXEMISSION"+maxDateText, MAXEMISSION);
						}
					}
					for(int i=0;i<RealEmission.size();i++) {
						RowBase realRow = RealEmission.get(i);
						String realMN = realRow.getSafeString("MN");
						String pollCode = realRow.getSafeString("POLLCODE");
						if(mn.equals(realMN)&&pollcode.equals(pollCode)) {
							String realDateText = realRow.getSafeString("DATE_TEXT");
							String REALEMISSION = realRow.getSafeString("REALEMISSION"+realDateText);
							rstRow.setValue("REALEMISSION"+realDateText, REALEMISSION);
						}
					}
					for(int m=0;m<ReductValueAndRate.size();m++) {
						RowBase rateRow = ReductValueAndRate.get(m);
						String rateMN = rateRow.getSafeString("MN");
						String pollCode = rateRow.getSafeString("POLLCODE");
						if(mn.equals(rateMN)&&pollcode.equals(pollCode)) {
							String rateDateText = rateRow.getSafeString("DATETEXT");
							String REDUCTVALUE = rateRow.getSafeString("REDUCTVALUE"+rateDateText);
							String REACUCTRATE = rateRow.getSafeString("REACUCTRATE"+rateDateText);
							rstRow.setValue("REDUCTVALUE"+rateDateText, REDUCTVALUE);
							rstRow.setValue("REACUCTRATE"+rateDateText, REACUCTRATE);
						}
					}
					rstRow.setValue("POLLCODE", pollcode);
					result.add(rstRow);
				}
			}
		return result;
	}
	/** 
     * @Title: exportExcel 
     * @Description: 导出Excel的方法 
     * @author: 20170604 xwei  
     * @param workbook  
     * @param sheetNum (sheet的位置，0表示第一个表格中的第一个sheet) 
     * @param sheetTitle  （sheet的名称） 
     * @param headers    （表格的标题） 
     * @param result   （表格的数据） 
     * @param out  （输出流） 
     * @throws Exception 
     */  
    public void exportWaterTypeExcel(HSSFWorkbook workbook, int sheetNum,  
            String sheetTitle, List<Object> headers, List<List<String>> result,  
            OutputStream out) throws Exception {  
        // 生成一个表格  
        HSSFSheet sheet = workbook.createSheet();  
        workbook.setSheetName(sheetNum, sheetTitle);  
        // 设置表格默认列宽度为20个字节  
        sheet.setDefaultColumnWidth((short) 20);  
        // 生成一个样式  
        HSSFCellStyle style = workbook.createCellStyle();  
        // 设置这些样式  
        style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);  
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
        // 生成一个字体  
        HSSFFont font = workbook.createFont();  
        font.setColor(HSSFColor.BLACK.index);  
        font.setFontHeightInPoints((short) 12);  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
        // 把字体应用到当前的样式  
        style.setFont(font);  
  
        // 指定当单元格内容显示不下时自动换行  
        style.setWrapText(true);  
  
        // 产生表格标题行  
        HSSFRow row = sheet.createRow(0);  
        for (int i = 0; i < headers.size(); i++) {  
            HSSFCell cell = row.createCell((short) i);  
          
            cell.setCellStyle(style);  
            HSSFRichTextString text = new HSSFRichTextString((String)headers.get(i));  
            cell.setCellValue(text.toString());  
        }  
        // 遍历集合数据，产生数据行  
        if (result != null) {  
            int index = 1;  
            for (List<String> m : result) {  
                row = sheet.createRow(index);  
                int cellIndex = 0;  
                for (String str : m) {  
                    HSSFCell cell = row.createCell((short) cellIndex);  
                    cell.setCellValue(str.toString());  
                    cellIndex++;  
                }  
                index++;  
            }  
        } 
        workbook.write(out);
    }  
    
    public HashMap<String,Object> queryTrendByYears(Date startDate,Date endDate,String cycleTypeStr,String[] stationCodes,HashMap<String,List<String>> mulitPolls) throws ParseException{
		String tableName = "";
		
		if ("day".equals(cycleTypeStr)) {
			tableName = "monitor_data_day";
		}else if ("week".equals(cycleTypeStr)) {
			tableName = "monitor_data_week";
		}else if("month".equals(cycleTypeStr)){
			tableName = "monitor_data_month";
		} else if ("quarter".equals(cycleTypeStr)) {
			tableName = "monitor_data_quarter";
		}else if ("year".equals(cycleTypeStr)) {
			tableName = "monitor_data_year";
		}
/*		ProduceWaterTypeByStadard(startDate,endDate,cycleTypeStr);*/
/*		produceCompPollIndexByStadard(startDate,endDate,cycleTypeStr);*/
		
		List<RowBase> riverNames = getMulitPollRiver(stationCodes,mulitPolls);
		List<RowBase> sections = getMulitPollRiverSection(stationCodes,mulitPolls);
		List<RowBase> stationNames = getMulitPollStations(stationCodes,mulitPolls);
		List<RowBase> pollNameList = getMulitPollNames(mulitPolls,stationNames);
		List<String> pollDeduplicated = getPollDeduplicated(pollNameList);
		List<RowBase> pollDeduplicatedList = getPollNames(pollDeduplicated.toArray(new String[pollDeduplicated.size()]));
		
		
		
		String[] stationMNs = new String[stationCodes.length];
		//获取mn集合
		for(int i=0;i<stationCodes.length;i++){
			String mn = RedisHelper.getInstance().getMNByStationCode(stationCodes[i]);
			stationMNs[i] = mn;
		}
		
		//获取mn的in where语句和参数
		String mnInWhere = StringUtil.getArrayStrForSql(stationMNs, "mn");
		List<GlobalParameter> pc1 = DataUtil.arrayToParameters(stationMNs, "mn");
		String pollCodeInWhere = StringUtil.getArrayStrForSql(pollDeduplicated.toArray(new String[pollDeduplicated.size()]), "pollCode");
		pc1.addAll(DataUtil.arrayToParameters(pollDeduplicated.toArray(new String[pollDeduplicated.size()]), "pollCode"));
		pc1.add(new GlobalParameter("startDate", startDate));
		pc1.add(new GlobalParameter("endDate", endDate));
		
		String dateTextsql = " SELECT distinct trunc(datatime) time ,DATE_TEXT FROM  "+tableName+" WHERE DATATIME >= @startDate and DATATIME <= @endDate "+
				 " and mn in "+mnInWhere +
				 " and parmname in "+pollCodeInWhere+" order by time,DATE_TEXT asc ";
		
		List<String> dateTextList = DataUtil.getSelectStringRows(dateTextsql, pc1, "DATE_TEXT");
		String dateTexts = "";
		for(String dateText : dateTextList){
			dateTexts += ",'"+dateText+"' P"+dateText;
		}
		dateTexts = dateTexts.replaceFirst(",", "");
		
		String sql = " select * from ( select * from (\n" +
						"  select value_type_ ,value,mn_ ,parmname_,date_text,RIVER_CODE_,RIVER_SECTION_ from (\n" + 
						"         select  max,min,avg,mn mn_ ,parmname parmname_,date_text,RIVER_CODE  RIVER_CODE_,RIVER_SECTION RIVER_SECTION_ from  "+tableName+ " md"+
						"			 left join BC_COLLECTION_INFO dc on dc.device_num = md.mn "+
						"			 left join BC_SITE_INFO ms on ms.code = dc.site_id  "+
						"			where md.parmname in "+pollCodeInWhere +
						"				and md.DATATIME >= @startDate " +
						"				and md.DATATIME <= @endDate \n" + 
						" 				and md.mn in "+mnInWhere+
						"  ) unpivot (value for value_type_ in (max,min,avg))\n" + 
						" )  pivot(avg(value) for date_text in ("+dateTexts+")) ) t";
		
		String forAllDataSql = "";
		for(RowBase station:stationNames){
			String mn = station.getSafeString("MN");
			String stationCode = station.getSafeString("CODE");
			String riverCode = station.getSafeString("RIVER_CODE");
			String riverSection = station.getSafeString("RIVER_SECTION");
			for(String pollCode:pollDeduplicated){
				forAllDataSql += "\n SELECT '"+stationCode+"' STATION_CODE, '"+mn+"' mn,'"+riverCode+"' river_code,'"+riverSection+"' river_section,'"+pollCode+"' parmname,'AVG' value_type FROM DUAL \n"
						+ " union all";
				forAllDataSql += "\n SELECT '"+stationCode+"' STATION_CODE, '"+mn+"' mn,'"+riverCode+"' river_code,'"+riverSection+"' river_section,'"+pollCode+"' parmname,'MAX' value_type FROM DUAL \n"
						+ " union all";
				forAllDataSql += "\n SELECT '"+stationCode+"' STATION_CODE, '"+mn+"' mn,'"+riverCode+"' river_code,'"+riverSection+"' river_section,'"+pollCode+"' parmname,'MIN' value_type FROM DUAL \n"
						+ " union all";
			}
		}
		forAllDataSql = StringUtil.replaceLast(forAllDataSql, "union all", "");
		forAllDataSql = " (SELECT * FROM ("+forAllDataSql+")) t1 ";
		
		sql = sql + " \n right join \n " + forAllDataSql + " on t.mn_ = t1.mn and t1.value_type=t.value_type_ and t.parmname_ = t1.parmname";
		
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("POLLDATA");
		List<RowBase> list = DataUtil.getSelectRows(sql, pc1,pTable);
		
		Iterator<RowBase> resultListItera = list.iterator();
		while(resultListItera.hasNext()){
			RowBase dataRow = resultListItera.next();
			String stationCode = dataRow.getSafeString("STATION_CODE");
			String curPollCode = dataRow.getSafeString("PARMNAME");
			String valueType = dataRow.getSafeString("VALUE_TYPE");
			List<String> curPollList = mulitPolls.get(stationCode);
			if(!curPollList.contains(curPollCode)){
				System.out.println(stationCode+","+curPollCode+","+valueType);
				resultListItera.remove();
			}
		}
		
		//对数据进行排序
		RowBaseCompare rowBaseComparer = new RowBaseCompare();
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("RIVER_CODE"));
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("RIVER_SECTION"));
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("MN"));
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("PARMNAME"));
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("VALUE_TYPE"));
		Collections.sort(list, rowBaseComparer);
		
		HashMap<String, Object> resultMapObject = new HashMap<String,Object>();
		resultMapObject.put("river", riverNames);
		resultMapObject.put("riverSection", sections);
		resultMapObject.put("stations", stationNames);
		resultMapObject.put("polls", pollNameList);
		resultMapObject.put("data", list);
		resultMapObject.put("dateTexts", dateTextList);
		resultMapObject.put("pollDeduplicated",pollDeduplicatedList);
		
		return resultMapObject;
	}
    /**
     * 根据站点code，分组统计河流有几个站点
     * @param stationCodes
     * @return
     */
    public List<RowBase> queryStationCount(String[] stationCodes) {
    	List<RowBase> stationCounts = new ArrayList<>();
    	String sql = "select t2.river_basin_name,t2.river_basin_code, t3.river_name,t3.river_code,count(*) station_count\n" +
    					" from BC_SITE_INFO t1\n" + 
    					" left join river_basin_info t2 on t1.river_basin_code =\n" + 
    					"                                  t2.river_basin_code\n" + 
    					" left join river_info t3 on t3.river_code = t1.river_code\n" + 
    					"where 1=1 \n ";
    	String codes="";
    	if(stationCodes!=null && stationCodes.length>0) {
    		for (int i = 0; i< stationCodes.length; i++) {
    			codes+="'"+stationCodes[i]+"',";
    		}
    		codes = codes.substring(0, codes.length()-1);
    	}
    	sql+= "and t1.code in ("+codes+")"+" group by  t2.river_basin_name,t2.river_basin_code, t3.river_name,t3.river_code";
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("POLLDATA");
		stationCounts = DataUtil.getSelectRows(sql, null,pTable);
    	return stationCounts;
    }
    
    /**
     * 根据站点code，分组统计流域有几条河流
     * @param stationCodes
     * @return
     */
    public List<RowBase> queryStationInfo(String[] stationCodes) {
    	List<RowBase> StationInfo = new ArrayList<>();
    	String codes="";
    	if(stationCodes!=null && stationCodes.length>0) {
    		for (int i = 0; i< stationCodes.length; i++) {
    			codes+="'"+stationCodes[i]+"',";
    		}
    		codes = codes.substring(0, codes.length()-1);
    	}
    	String sql = "select t1.name,t1.code, t2.river_code, t2.river_name\n" +
    					"  from BC_SITE_INFO t1\n" + 
    					"  left join river_info t2 on t1.river_code = t2.river_code\n" + 
    					" where t1.code in ("+codes+")"+" order by t2.river_code";

		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("POLLDATA");
		StationInfo = DataUtil.getSelectRows(sql, null,pTable);
    	return StationInfo;
    }
    
    /**
     * 根据站点code，分组统计流域的站点数目以及河流的站点数目。
     * @param stationCodes
     * @return
     */
    public List<RowBase> queryBasinCountAndRiverCount(String[] stationCodes) {
    	String codes="";
    	if(stationCodes!=null && stationCodes.length>0) {
    		for (int i = 0; i< stationCodes.length; i++) {
    			codes+="'"+stationCodes[i]+"',";
    		}
    		codes = codes.substring(0, codes.length()-1);
    	}
    	List<RowBase> riverCounts = new ArrayList<>();
    	String sql = "select tt1.*, tt2.*,tt1.station_count*tt2.river_count as basinTotal_count from (select t2.river_basin_name,t2.river_basin_code, t3.river_name,t3.river_code,count(*) station_count\n" +
    					" from BC_SITE_INFO t1\n" + 
    					" left join river_basin_info t2 on t1.river_basin_code =\n" + 
    					"                                  t2.river_basin_code\n" + 
    					" left join river_info t3 on t3.river_code = t1.river_code\n" + 
    					"where t1.code in ("+codes+") group by  t2.river_basin_name,t2.river_basin_code, t3.river_name,t3.river_code) tt1\n" + 
    					"left join\n" + 
    					"(select river_basin_name,river_basin_code,count(*) river_count from (select t2.river_basin_name,t2.river_basin_code, t3.river_name,t3.river_code,count(*)\n" + 
    					" from BC_SITE_INFO t1\n" + 
    					" left join river_basin_info t2 on t1.river_basin_code =\n" + 
    					"                                  t2.river_basin_code\n" + 
    					" left join river_info t3 on t3.river_code = t1.river_code\n" + 
    					"where t1.code in ("+codes+") group by  t2.river_basin_name,t2.river_basin_code, t3.river_name,t3.river_code) group by  river_basin_name, river_basin_code)tt2\n" + 
    					" on tt1.river_basin_code=tt2.river_basin_code";
    	
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("POLLDATA");
		riverCounts = DataUtil.getSelectRows(sql, null,pTable);
    	return riverCounts;
    }
    /**
     * 获取指定污染物的VI类水质标准的最大值。
     * @param pollCodes
     * @return
     */
    public List<RowBase> queryPollVITypeMaxMun(List<String> pollCodes) {
    	List<RowBase> result = new ArrayList<>();
    	String codeStr="";
    	if(pollCodes!=null && pollCodes.size()>0) {
        	for(int i=0;i<pollCodes.size();i++) {
        		String code = pollCodes.get(i);
        		codeStr+="'"+code+"',";
        	}
        	codeStr=codeStr.substring(0, codeStr.length()-1);
    	}
    	String sql = 
    			"select t.item, t.quality_class, trunc(t.maximum, 2) maxmum\n" +
    					"  from BC_QUALITY_STANDARD t\n" + 
    					" where t.quality_class = '5'\n" + 
    					"   and t.category = 'SURFACE_WATER'\n" + 
    					"   and item in ("+codeStr+")";
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("POLLDATA");
		result = DataUtil.getSelectRows(sql, null,pTable);
		return result;
    }
    public int getSeason(Date date) {  
    	  
        int season = 0;  
  
        Calendar c = Calendar.getInstance();  
        c.setTime(date);  
        int month = c.get(Calendar.MONTH);  
        switch (month) {  
        case Calendar.JANUARY:  
        case Calendar.FEBRUARY:  
        case Calendar.MARCH:  
            season = 1;  
            break;  
        case Calendar.APRIL:  
        case Calendar.MAY:  
        case Calendar.JUNE:  
            season = 2;  
            break;  
        case Calendar.JULY:  
        case Calendar.AUGUST:  
        case Calendar.SEPTEMBER:  
            season = 3;  
            break;  
        case Calendar.OCTOBER:  
        case Calendar.NOVEMBER:  
        case Calendar.DECEMBER:  
            season = 4;  
            break;  
        default:  
            break;  
        }  
        return season;  
    } 
    /**
     * 获取某年第一天
     * @param year
     * @return
     */
    public  Date getCurrYearFirst(int year){  
        Calendar calendar = Calendar.getInstance();  
        calendar.clear();  
        calendar.set(Calendar.YEAR, year);  
        Date currYearFirst = calendar.getTime();  
        return currYearFirst;  
    }
    /**
     * 获取某年最后一天日期
     * @param year
     * @return
     */
    public Date getCurrYearLast(int year){  
        Calendar calendar = Calendar.getInstance();  
        calendar.clear();  
        calendar.set(Calendar.YEAR, year);  
        calendar.roll(Calendar.DAY_OF_YEAR, -1);  
        Date currYearLast = calendar.getTime();  
          
        return currYearLast;  
    }  
	
	
}
